NOT EXIST和NOT IN 和MINUS的用法

最新推荐文章于 2025-08-21 15:38:50 发布

原创最新推荐文章于 2025-08-21 15:38:50 发布 · 1.3k 阅读

1 ·

CC 4.0 BY-SA版权

ORACLE技术笔记专栏收录该内容

30 篇文章

订阅专栏

本文探讨了在SQL查询中使用NOTIN和MINUS操作符优化查询性能的方法，通过实例展示了如何避免使用NOT EXISTS，进而提高查询速度。重点介绍了在特定场景下，利用这些操作符可以显著减少查询时间。

①NOT EXISTS一般都会比较慢

UPDATE A
SET DATE=sysdate
WHERE LINK_TAISHOGAI_FG = '0'
    AND NOT EXISTS
(SELECT 1
FROM   FROM   B
WHERE A.ID1= B.ID1
AND    A.ID2 = B.ID2
AND   A.ID3 = B.ID3
AND    B.ID4 IN ('A','B','C')
)

②上面的改为NOT IN

UPDATE A
SET DATE=sysdate
WHERE LINK_TAISHOGAI_FG = '0'
AND (A.ID1,A.ID2,A.ID3) NOT IN
(SELECT B.ID1,B.ID2,B.ID3
FROM   B
WHERE A.ID1= B.ID1
AND    A.ID2 = B.ID2
AND   A.ID3 = B.ID3
AND    B.ID4 IN ('A','B','C')
)

③加入MINUS，能缩短时间

A表的数据如果在BC表里不存在的话将其其中一个字段更新

UPDATE A
SET DATE=sysdate
WHERE LINK_TAISHOGAI_FG = '0'
AND (A.ID1,A.ID2,A.ID3) IN
(
select A.ID1,A.ID2,A.ID3 FROM A
where LINK_TAISHOGAI_FG = '0'
MINUS
select temp.ID1,temp.ID2,temp.ID3
FROM
(
select B.ID1,B.ID2,B.ID3
FROM B
where B.ID4 IN ('A','B','C')
UNION ALL
select C.ID1,C.ID2,C.ID3
FROM C
where C.ID4 IN ('AA','BB','CC')
)
)