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

原创于 2022-10-31 18:41:45 发布 · 1.1k 阅读

CC 4.0 BY-SA版权

文章标签：

20 篇文章

订阅专栏

博客主要介绍了SQL中使用EXISTS的几种情况，包括用EXISTS替换IN、替换查询子句、代替COUNT判断条件数据是否存在，以及使用NOT EXISTS查询父结果集有而子结果集没有的记录，还提及3条SQL性能依次降低。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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)

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)

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

替换为

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

以下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)