oracle用两个in查询特别慢,ORACLE查询IN的优化问题-优快云博客

本文探讨了SQL查询中IN、EXISTS和JOIN子查询在性能上的差异。作者分享了一种原本使用IN子句的查询，发现执行速度慢，改用EXISTS后没有显著提升。最后通过表连接的方式实现了更快的查询速度，但结果数据出现不一致。问题在于如何正确优化SQL以获取准确且快速的查询结果。

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

原来用IN写的sql：

SELECT

O.ID,

(SELECT COUNT(1) FROM USER_LOGIN UL JOIN USER U ON U.IDCARD=UL.IDCARD WHERE U.DEPTID IN(SELECT ID FROM ORG WHERE TYPE!='03' START WITH ID=O.ID CONNECT BY PRIOR ID=PID)) AS TOTALLOGIN

FROM ORG O WHERE O.PID='1000' AND O.TYPE!='03' ORDER BY ID

用IN写的非常慢118s，然后改成EXISTS：

SELECT

O.ID,

(SELECT COUNT(1) FROM USER_LOGIN UL JOIN USER U ON U.IDCARD=UL.IDCARD WHERE EXISTS(SELECT ID FROM ORG O1 WHERE O1.TYPE!='03' AND O1.ID=U.DEPT START WITH O1.ID=O.ID CONNECT BY PRIOR O1.ID=O1.PID)) AS TOTALLOGIN

FROM ORG O WHERE O.PID='1000' AND O.TYPE!='03' ORDER BY O.ID

用EXISTS写的也需要118s左右，然后我改成表连接

SELECT

O.ID,

(SELECT COUNT(1) FROM USER_LOGIN UL JOIN USER U ON U.IDCARD=UL.IDCARD JOIN (SELECT ID FROM ORG) A WHERE A.TYPE!='03' START WITH A.ID=O.ID CONNECT BY PRIOR A.ID=A.PID) AS TOTALLOGIN

FROM ORG O WHERE O.PID='1000' AND O.TYPE!='03' ORDER BY O.ID

速度快了很多，但数据和上面的不一样，感觉少数据，这个不知道是什么问题，各位老哥帮忙看一下