使用exists和IN执行sql查询 [英] sql query execution using exists and IN

问题描述

SELECT * FROM FAMILY_DETAILS WHERE EXISTS(SELECT EMPLOYEE_ID FROM EMPLOYEE_MASTER WHERE EMPLOYEE_ID='EMP001')

SELECT * FROM FAMILY_DETAILS WHERE EMPLOYEE_ID IN(SELECT EMPLOYEE_ID FROM EMPLOYEE_MASTER WHERE EMPLOYEE_ID='EMP001')

I have these two queries in sql server 2008 R2. Both will give same results. Actually from these above queries output is only two rows, But in case if i have large number of records in that scenario Which one is better to use? and why?

推荐答案

我建​​议阅读这些文章：

http://sqlinthewild.co.za /index.php/2009/08/17/exists-vs-in/ [ ^ ]

http://www.gregreda.com/2013/06/03/join-vs-exists-vs-in/ [ ^ ]

http://explainextended.com/2009/06/16/in-vs -join-vs-exists / [ ^ ]

http： //网页logs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx [ ^ ]

I'd suggest to read these articles:
http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/[^]
http://www.gregreda.com/2013/06/03/join-vs-exists-vs-in/[^]
http://explainextended.com/2009/06/16/in-vs-join-vs-exists/[^]
http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx[^]

当你有大量数据时，这些查询的效率都非常低。请改用JOIN，并在员工ID上加入FAMILY_DETAILS和EMPLOYEE_MASTER。

Use neither, when you have a lot of data these queries will be very inefficient. Use JOINs instead and join FAMILY_DETAILS and EMPLOYEE_MASTER on employee id.

两者都是错误的选项，使用嵌套子查询会影响性能。而不是尝试使用可以提供更好性能的连接。将subwueries替换为等效的连接 [ ^ ]

Both are wrong options, using nested subqueries will impact the performance. Instead of that try to use joins that will give you better performance.Replace subwueries with equivalent joins[^]

这篇关于使用exists和IN执行sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！