试试这个: SELECT code FROM A minus SELECT code1 from B
如果是 SELECT code FROM A WHERE code NOT IN (SELECT code1 from B WHERE code1 IS NOT NULL) A中所有的数据都被检索出来了(即使B中有的数据)
确实是楼主说的那样 包含null的就不行 null 无法和 数据进行比较从这个现象来看, 我估计not in 或者in实际上数据库进行处理时 是逐项进行比较的SELECT code FROM A WHERE code <> null; 结果为空
试验下 SELECT nvl(code,'') FROM A WHERE nvl(code,'') NOT IN ((SELECT code1 from B WHERE code1 IS NOT NULL))
code和code1两边为空如何做比较。
若A表中的code为主键,那该语句查询条伯才成立,怎么可以为空。 SELECT code FROM A WHERE not exists(SELECT 1 from B where code=code1 and code1 is not null)若不为主键: SELECT code FROM A WHERE not exists(SELECT 1 from B where code=code1 and code1 is not null) and code is not null union SELECT code FROM A where code is null;
SELECT code FROM A
minus
SELECT code1 from B
SELECT code
FROM A
WHERE code NOT IN (SELECT code1 from B WHERE code1 IS NOT NULL)
A中所有的数据都被检索出来了(即使B中有的数据)
包含null的就不行
null 无法和 数据进行比较从这个现象来看,
我估计not in 或者in实际上数据库进行处理时
是逐项进行比较的SELECT code
FROM A
WHERE code <> null;
结果为空
SELECT nvl(code,'')
FROM A
WHERE nvl(code,'') NOT IN ((SELECT code1 from B WHERE code1 IS NOT NULL))
SELECT code FROM A
WHERE not exists(SELECT 1 from B where code=code1 and code1 is not null)若不为主键:
SELECT code FROM A
WHERE not exists(SELECT 1 from B where code=code1 and code1 is not null)
and code is not null
union
SELECT code FROM A where code is null;