我有两张表A,B ， 两张表都有number字段 ，但内容只有一部分是相同的。请问我该如何查询B中所有在A中不存在的number ?
ç¨not inè¯å¥æ¥å®ç°ã
1ãæµè¯è¡¨å建ï¼æå ¥æ°æ®ï¼
create table a(id int,
name varchar2(10));
create table b
(id int);
insert into a values (1,'a');
insert into a values (2,'b');
insert into a values (3,'c');
insert into a values (4,'d');
insert into a values (5,'e');
insert into b values (1);
insert into b values (3);
2ãæ¥è¯¢b表ä¸åå¨çidä¸åºç°å¨a表ä¸çå
容ï¼å¯ç¨è¯å¥ï¼
3ãç»æï¼
温馨提示：答案为网友推荐，仅供参考
第1个回答 2020-02-05
1、创建测试表，
create table test_tbl_a(num number);
create table test_tbl_b(num number);
2、插入测试数据；
insert into test_tbl_a values (1);
insert into test_tbl_a values (2);
insert into test_tbl_b values (1);
insert into test_tbl_b values (2);
insert into test_tbl_b values (3);
insert into test_tbl_b values (4);
commit;
3、查询B表中全量数据；select t.*, rowid from test_tbl_b t;
4、编写语句，查询B中所有在A中不存在的number；
select t.* from test_tbl_b t where not exists (select 1 from test_tbl_a b where t.num = b.num)
本回答被网友采纳第2个回答 推荐于2017-09-25
select * from B where number not in (select number from A)
或
select B.* from B left join A on A.numer=B.number
where A.number is null本回答被提问者采纳
或
select B.* from B left join A on A.numer=B.number
where A.number is null本回答被提问者采纳
第3个回答 2011-08-10
select number from b where not number in (select number from a)
