有两个表TABLE1和TABLE2,其中有一个公共字段ID。我想根据ID值从TABLE2中检索与TABLE1不匹配的值。
select * from TABLE2 where subject = 1 and ID NOT IN (select ID from TABLE1 where subject = 1)
示例:TABLE1
ID SUBJECT
1 1TABLE2
ID SUBJECT
1 1
2 1
预期结果是2,并且运行良好。
但是当TABLE1为空或者内部 select ID from TABLE1 where subject = 1
返回空时,整个select语句返回空。但是预期的结果是1,2
有没有办法做到这一点?
使用left join
select t2.*
from table2 t2
left outer join table1 t1 on t1.id = t2.id and t1.subject = 1
where t2.subject = 1
and t1.id is null
查看连接的详细说明
我认为你也可以使用非exists
-
select * from TABLE2 where subject = 1 and NOT exists
(select 1 from TABLE1 where subject = 1 and table1.id = table2.id)