Question

SQL中IN和EXISTS用法的区别

Answer 1

　　EXISTS will tell you whether a query returned any results. eg: SELECT * FROM Orders o WHERE EXISTS ( SELECT * FROM Products p where p.ProductNumber = o.ProductNumber) IN is used to compare one value to several, and can use literal values, like this: SELECT * FROM Orders WHERE ProductNumber IN (1, 10, 100) You can also use query results with the IN clause, like this: SELECT * FROM Orders WHERE ProductNumber IN ( SELECT ProductNumber FROM Products WHERE ProductInventoryQuantity > 0) EXISTS is much faster than IN when the subquery results is very large. IN is faster than EXISTS when the subquery results is very small. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e52131e3-761e-4a11-b725-ae585ed1f115/difference-between-in-and-exists?forum=transactsql