Advertisement
Advertisement


Difference between EXISTS and IN in SQL?


Question

What is the difference between the EXISTS and IN clause in SQL?

When should we use EXISTS, and when should we use IN?

2014/10/21
1
445
10/21/2014 12:59:51 AM


EXISTS will tell you whether a query returned any results. e.g.:

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)
2017/08/19

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:

  • There is no difference.
2015/08/18

I'm assuming you know what they do, and thus are used differently, so I'm going to understand your question as: When would it be a good idea to rewrite the SQL to use IN instead of EXISTS, or vice versa.

Is that a fair assumption?


Edit: The reason I'm asking is that in many cases you can rewrite an SQL based on IN to use an EXISTS instead, and vice versa, and for some database engines, the query optimizer will treat the two differently.

For instance:

SELECT *
FROM Customers
WHERE EXISTS (
    SELECT *
    FROM Orders
    WHERE Orders.CustomerID = Customers.ID
)

can be rewritten to:

SELECT *
FROM Customers
WHERE ID IN (
    SELECT CustomerID
    FROM Orders
)

or with a join:

SELECT Customers.*
FROM Customers
    INNER JOIN Orders ON Customers.ID = Orders.CustomerID

So my question still stands, is the original poster wondering about what IN and EXISTS does, and thus how to use it, or does he ask wether rewriting an SQL using IN to use EXISTS instead, or vice versa, will be a good idea?

2016/07/08

  1. 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.

    CREATE TABLE t1 (id INT, title VARCHAR(20), someIntCol INT)
    GO
    CREATE TABLE t2 (id INT, t1Id INT, someData VARCHAR(20))
    GO
    
    INSERT INTO t1
    SELECT 1, 'title 1', 5 UNION ALL
    SELECT 2, 'title 2', 5 UNION ALL
    SELECT 3, 'title 3', 5 UNION ALL
    SELECT 4, 'title 4', 5 UNION ALL
    SELECT null, 'title 5', 5 UNION ALL
    SELECT null, 'title 6', 5
    
    INSERT INTO t2
    SELECT 1, 1, 'data 1' UNION ALL
    SELECT 2, 1, 'data 2' UNION ALL
    SELECT 3, 2, 'data 3' UNION ALL
    SELECT 4, 3, 'data 4' UNION ALL
    SELECT 5, 3, 'data 5' UNION ALL
    SELECT 6, 3, 'data 6' UNION ALL
    SELECT 7, 4, 'data 7' UNION ALL
    SELECT 8, null, 'data 8' UNION ALL
    SELECT 9, 6, 'data 9' UNION ALL
    SELECT 10, 6, 'data 10' UNION ALL
    SELECT 11, 8, 'data 11'
    
  2. Query 1

    SELECT
    FROM    t1 
    WHERE   not  EXISTS (SELECT * FROM t2 WHERE t1.id = t2.t1id)
    

    Query 2

    SELECT t1.* 
    FROM   t1 
    WHERE  t1.id not in (SELECT  t2.t1id FROM t2 )
    

    If in t1 your id has null value then Query 1 will find them, but Query 2 cant find null parameters.

    I mean IN can't compare anything with null, so it has no result for null, but EXISTS can compare everything with null.

2012/07/04

If you are using the IN operator, the SQL engine will scan all records fetched from the inner query. On the other hand if we are using EXISTS, the SQL engine will stop the scanning process as soon as it found a match.


Source: https://stackoverflow.com/questions/24929
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]