A表和B表通过ID1和ID2关联,怎么样才能查出来A表中不在B表中的数据？
今天同事提出了一个问题
条件：A表和B表通过ID1和ID2关联
目标：怎么样才能查出来A表中不在B表中的数据？
形象点，我把这两个表创建出来。
create table A ( id1 VARCHAR2(100), id2 VARCHAR2(100), infoA VARCHAR2(100), onlyA VARCHAR2(100) ); create table B ( id1 VARCHAR2(100), id2 VARCHAR2(100), infoB VARCHAR2(100), onlyB VARCHAR2(100) );
然后填入一些数据，为了方便辨识，同时故意留下一些空值作为特殊情况，在onlyA、onlyB字段手工标识出这个id1、id2是否只在A表或者B表出现
插入的数据如下
prompt Importing table A... set feedback off set define off insert into A (ID1, ID2, INFOA, ONLYA) values ('1', '001', 'A信息1', 'both'); insert into A (ID1, ID2, INFOA, ONLYA) values ('1', '002', 'A信息2', 'yes'); insert into A (ID1, ID2, INFOA, ONLYA) values ('1', '003', 'A信息3', 'yes'); insert into A (ID1, ID2, INFOA, ONLYA) values ('2', '001', 'A信息4', 'both'); insert into A (ID1, ID2, INFOA, ONLYA) values ('2', '002', 'A信息5', 'both'); insert into A (ID1, ID2, INFOA, ONLYA) values ('2', '003', 'A信息6', 'both'); insert into A (ID1, ID2, INFOA, ONLYA) values ('3', '001', 'A信息7', 'yes'); insert into A (ID1, ID2, INFOA, ONLYA) values ('3', null, 'A信息8', 'both'); insert into A (ID1, ID2, INFOA, ONLYA) values ('4', '003', 'A信息9', 'yes'); insert into A (ID1, ID2, INFOA, ONLYA) values (null, '001', 'A信息10', 'both'); insert into A (ID1, ID2, INFOA, ONLYA) values ('5', null, 'A信息11', 'yes'); insert into B (ID1, ID2, INFOB, ONLYB) values ('1', '001', '有对照', 'both'); insert into B (ID1, ID2, INFOB, ONLYB) values ('1', '004', 'nothing', 'yes'); insert into B (ID1, ID2, INFOB, ONLYB) values ('1', null, 'nothing', 'yes'); insert into B (ID1, ID2, INFOB, ONLYB) values ('2', '001', '有对照', 'both'); insert into B (ID1, ID2, INFOB, ONLYB) values ('2', '002', '有对照', 'both'); insert into B (ID1, ID2, INFOB, ONLYB) values ('2', '003', '有对照', 'both'); insert into B (ID1, ID2, INFOB, ONLYB) values ('3', null, '有对照', 'both'); insert into B (ID1, ID2, INFOB, ONLYB) values (null, '001', '有对照', 'both'); insert into B (ID1, ID2, INFOB, ONLYB) values ('6', '001', 'nothing', 'yes'); insert into B (ID1, ID2, INFOB, ONLYB) values (null, '007', 'nothing', 'yes'); prompt Done.
同事们给出方案若干个：
1、把这两个字段拼成一个，然后用not in
select id1,id2,infoA,id1||id2 as tempA,onlyA from a where id1||id2 not in (select id1||id2 as TempB from B)
结果5条，符合预期。
但这个方法的问题是：id1和id2的编码形式是有明显区别的，如果都是三位的数字，那么新字段tempA、tempB该加个下划线或者符号进行区隔
2、使用(字段1，字段2)这种对象进行比较
select id1,id2,infoA,onlyA from a where (id1,id2) not in (select id1,id2 from b ) order by id1,id2
这样的结果很奇怪，只有一条，丢掉了4条，分析其中原因，我们把其中的not 去掉
select id1,id2,infoA,onlyA from a where (id1,id2) in (select id1,id2 from b ) order by id1,id2
这里的both也没有列全，丢掉了两条，这两条是null,001和3,null
也就是这种用法会因为id1=1,3内容的id2包含null而被滤掉，id1=5的本身id2就是null也被滤掉，最终只能展示1条符合条件的内容，如下图
也就是这种描述方法会造成空字段会被滤掉，丢结果，但也是有条件的
（1）在业务数据中，id1和id2数据合法性可以保证不含空字符串的情况，（如果含空格也不会造成丢数据，只有null会丢）
（2）可以改造一下后使用，将null值进行转换，然后使用
select id1,id2,infoA,onlyA from a where (nvl(id1,' '),nvl(id2,' ')) not in (select nvl(id1,' '),nvl(id2,' ') from b ) order by id1,id2
其他方案1
因为只筛选id1，造成有漏项有多项
select a.id1,a.id2,a.infoa,a.onlya from a where a.id1 not in (select a.id1 from a inner join b on a.id1=b.id1 and a.id2=b.id2)
其他方案2
select * from A not in (select * from a join b on xxx=xxx)
有兴趣的朋友可以试一下
