查询一个表中的字段不在另一个表中Table1
A B C
1 ds a
1 fd d
2 dfs NULL
3 sd NULL
Table2
AA BB CC DD
2 w w NULL
1 dd sd NULL
1 sdd sf dselect * from Table1 where C not in
(select DD From Table2)
结果却查不到数据
A B C
1 ds a
1 fd d
2 dfs NULL
3 sd NULL
Table2
AA BB CC DD
2 w w NULL
1 dd sd NULL
1 sdd sf dselect * from Table1 where C not in
(select DD From Table2)
结果却查不到数据
(select DD From Table2 where DD is not null)
insert into table1 values(1 ,'ds' ,'a')
insert into table1 values(1 ,'fd' ,'d')
insert into table1 values(2 ,'dfs' ,NULL)
insert into table1 values(3 ,'sd' ,NULL)
create table Table2(AA int,BB varchar(10), CC varchar(10),DD varchar(10))
insert into table2 values(2, 'w' , 'w' ,NULL)
insert into table2 values(1, 'dd' , 'sd' ,NULL)
insert into table2 values(1, 'sdd', 'sf' ,'d')
goselect m.* from table1 m where c not in (select dd from table2 where dd is not null)
/*
A B C
----------- ---------- ----------
1 ds a(所影响的行数为 1 行)
*/select m.* from table1 m where c is null or c not in (select dd from table2 where dd is not null)
/*
A B C
----------- ---------- ----------
1 ds a
2 dfs NULL
3 sd NULL(所影响的行数为 3 行)
*/drop table table1,table2
select * from Table1 where C not in
(select DD From Table2)--转换为
select * from Table1
where C != NULL and C != NULL and C != 'd' --C !=DD--C != NULL这个是取不到值的,所以返回0条结果