Mysql使用exist和not exist的场景举例

例1：用exists替换in

select num from a where num in(select num from b) 

替换为

select num from a where exists(select 1 from b where num=a.num)

例2：用exists替换查询子句

SELECT SUM(T1.C1) FROM T1 WHERE (SELECT COUNT(*)FROM T2 WHERE T2.C2=T1.C2)>0

替换为

SELECT SUM(T1.C1) FROM T1 WHERE EXISTS(SELECT * FROM T2 WHERE T2.C2=T1.C2)

例3：用exists代替count判断是否存在某种条件数据

IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')

替换为

IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')

例4：查询父结果集中有而子结果集中没有的记录（not exists）

以下3条sql性能依次降低

SELECT a.hdr_key FROM hdr_tbl a WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key)

SELECT a.hdr_key FROM hdr_tbl a LEFT JOIN dtl_tbl b ON a.hdr_key = b.hdr_key WHERE b.hdr_key IS NULL

SELECT hdr_key FROM hdr_tbl WHERE hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl)