--exists (sql 返回结果集,为真) --not exists (sql 不返回结果集,为真) --如下:
--表A ID NAME 1 A1 2 A2 3 A3-- 表B
ID AID NAME
1 1 B1
2 2 B2
3 2 B3 -- 表A和表B是1对多的关系 A.ID => B.AID
SELECT ID,NAME FROM A WHERE EXIST ( SELECT * FROM B WHERE A.ID = B.AID)
-- 执行结果为
1 A1
2 A2 -- 原因可以按照如下分析
SELECT ID,NAME FROM A WHERE EXISTS ( SELECT * FROM B WHERE B.AID = 1 )
-- -> SELECT * FROM B WHERE B.AID=1有值,返回真,所以有数据
SELECT ID,NAME FROM A WHERE EXISTS ( SELECT * FROM B WHERE B.AID = 2 )
-- -> SELECT * FROM B WHERE B.AID=2有值,返回真,所以有数据
SELECT ID,NAME FROM A WHERE EXISTS ( SELECT * FROM B WHERE B.AID = 3 )
-- -> SELECT * FROM B WHERE B.AID=3无值,返回假,所以没有数据
--NOT EXISTS 就是反过来 SELECT ID,NAME FROM A WHERE NOT EXIST (SELECT * FROM B WHERE A.ID=B.AID) --执行结果为 3 A3
表结构:temp.customer(cust_num) 主键:cust_num
表结构:temp.contact(cnt_id,cust_num) 主键:cnt_id
表结构:temp.contact_detail(cnt_id,address,phone) 主键:cnt_id
查询 :
select cust_num from temp.customer cust
where not exists (select 1 from temp.contact cont
where cust.cust_num = cont.cust_num)
此查询用来列出所有不存在联系人的客户