Разница между EXISTS и IN в SQL?

Ключевое слово exists можно использовать таким образом, но на самом деле оно предназначено для того, чтобы избежать подсчета голосов:

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

Это наиболее полезно, когда у вас есть if условных операторов, так как exists может быть намного быстрее, чем count .

in лучше всего использовать там, где у вас есть статический список для передачи:

 select * from [table]
 where [field] in (1, 2, 3)

Когда у вас есть таблица в операторе in , имеет больше смысла использовать join, но в основном это не должно иметь значения. Оптимизатор запросов должен возвращать один и тот же план в любом случае. В некоторых реализациях (в основном более старых, таких как Microsoft SQL Server 2000) in запросов всегда будет получать вложенный план соединения , в то время как join запросов будет использовать вложенные, слияние или hash соответственно. Более современные реализации умнее и могут корректировать план даже при использовании in .

EXISTS сообщит вам, вернул ли запрос какие-либо результаты. напр.:

SELECT * 
FROM Orders o 
WHERE EXISTS (
    SELECT * 
    FROM Products p 
    WHERE p.ProductNumber = o.ProductNumber)

IN используется для сравнения одного значения с несколькими и может использовать литеральные значения, например:

SELECT * 
FROM Orders 
WHERE ProductNumber IN (1, 10, 100)

Вы также можете использовать результаты запроса с предложением IN , например:

SELECT * 
FROM Orders 
WHERE ProductNumber IN (
    SELECT ProductNumber 
    FROM Products 
    WHERE ProductInventoryQuantity > 0)

На основе оптимизатора правил :

• EXISTS намного быстрее , чем IN, когда результаты подзапроса очень велики.
• IN быстрее , чем EXISTS, когда результаты подзапроса очень малы.

На основе стоимостного оптимизатора :

• Здесь нет никакой разницы.

Я предполагаю, что вы знаете, что они делают, и поэтому используются по-разному, поэтому я собираюсь понять ваш вопрос следующим образом: когда было бы хорошей идеей переписать SQL, чтобы использовать IN вместо EXISTS, или наоборот.

Разве это справедливое предположение?

Edit: причина, по которой я спрашиваю, заключается в том, что во многих случаях вы можете переписать SQL на основе IN, чтобы использовать вместо него EXISTS, и наоборот, и для некоторых ядер баз данных оптимизатор запросов будет относиться к ним по-разному.

Например:

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

можно переписать на:

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

или с помощью соединения:

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

Таким образом, мой вопрос все еще остается в силе: интересно ли оригинальному плакату, что делает IN и EXISTS, и, следовательно, как его использовать, или он спрашивает, Будет ли переписывать SQL, используя IN, чтобы использовать EXISTS вместо этого, или наоборот, будет хорошей идеей?

1. EXISTS намного быстрее, чем IN , когда результат подзапроса очень велик.
   IN быстрее, чем EXISTS , когда результат подзапроса очень мал.

   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. Запрос 1

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

   Запрос 2

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

   Если в t1 ваш id имеет значение null, то запрос 1 найдет их, но запрос 2 не может найти параметры null.

   Я имею в виду, что IN ничего не может сравнить с null, поэтому у него нет результата для null, но EXISTS может сравнить все с null.

Если вы используете оператор IN , механизм SQL будет сканировать все записи, извлеченные из внутреннего запроса. С другой стороны , если мы используем EXISTS, движок SQL остановит процесс сканирования, как только он найдет совпадение.