Difference between EXISTS and IN in SQL?
What is the difference between the EXISTS and IN clause in SQL?
When should we use EXISTS , and when should we use IN ?
The exists keyword can be used in that way, but really it's intended as a way to avoid counting:
--this statement needs to check the entire table
select count(*) from [table] where ...
--this statement is true as soon as one match is found
exists ( select * from [table] where ... )
This is most useful where you have if conditional statements, as exists can be a lot quicker than count .
The in is best used where you have a static list to pass:
select * from [table]
where [field] in (1, 2, 3)
When you have a table in an in statement it makes more sense to use a join , but mostly it shouldn't matter. The query optimiser should return the same plan either way. In some implementations (mostly older, such as Microsoft SQL Server 2000) in queries will always get a nested join plan, while join queries will use nested, merge or hash as appropriate. More modern implementations are smarter and can adjust the plan even when in is used.
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)
Based on rule optimizer:
EXISTS is much faster than IN , when the sub-query results is very large. IN is faster than EXISTS , when the sub-query results is very small. Based on cost optimizer:
下一篇: SQL中EXISTS和IN之间的区别？