现有三张表,表的结构如下:
table A:
NUMBER
10001
10002
10009
10005
10004table B:
NUMBER
10002
10007
10004table C:
NUMBER
10009
10008
10006
现在要查出既不在B表也不再C表中的A表中数据,即查询结果为:
NUMBER
10001
10005
这样的SQL语句该怎么写啊?
table A:
NUMBER
10001
10002
10009
10005
10004table B:
NUMBER
10002
10007
10004table C:
NUMBER
10009
10008
10006
现在要查出既不在B表也不再C表中的A表中数据,即查询结果为:
NUMBER
10001
10005
这样的SQL语句该怎么写啊?
where a.number not in (
select b.number from b
)
and a.number not in (
select c.number from c
)
试试这个
not exist(select * from b,c)
from a
left join b on a.number=b.number
left join c on a.number=c.number
where b.number is null and c.number is null
not in(select number from b,c)
select a from
(
select 1 a from dual
union all
select 2 from dual
union all
select 3 from dual
union all
select 4 from dual
union all
select 5 from dual
union all
select 6 from dual) a
minus
select a from
(
select 4 a from dual
union all
select 5 from dual) b
minus
select a from
(
select 2 a from dual
union all
select 3 from dual
union all
select 4 from dual
) c