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

Это справедливое предположение?

Редактировать: Причина, по которой я спрашиваю, заключается в том, что во многих случаях вы можете переписать 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 или наоборот?

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 - это когда вам нужно сопоставить результаты запроса с другим подзапросом. Результаты запроса №1 должны быть получены там, где совпадают результаты подзапроса. Вид соединения .. Например. выберите таблицу клиентов №1, которые также разместили заказы в таблице №2

• IN - получить, если значение определенного столбца лежит в IN, в списке (1,2,3,4,5) Например. Выберите клиентов, которые находятся в следующих почтовых индексах, т.е. значения zip_code находятся в списке (....).

Когда использовать одно вместо другого ... когда вы чувствуете, что он читается правильно (лучше передает намерение).

Ключевое слово 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 будут использовать вложенные, слить или хэш в зависимости от ситуации. Более современные реализации умнее и могут корректировать план даже при использовании in.

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

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

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

• Нет никакой разницы.

Насколько мне известно, когда подзапрос возвращает значение NULL, тогда весь оператор становится NULL. В этих случаях мы используем ключевое слово EXITS. Если мы хотим сравнить определенные значения в подзапросах, мы используем ключевое слово IN.

Ключевое слово Exists оценивает истину или ложь, а ключевое слово IN сравнивает все значения в соответствующем столбце подзапроса. Еще один Select 1 можно использовать с командой Exists. Пример:

SELECT * FROM Temp1 where exists(select 1 from Temp2 where conditions...)

Но IN менее эффективен, поэтому Exists быстрее.

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

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 ваш идентификатор имеет нулевое значение, тогда запрос 1 найдет их, но запрос 2 не может найти нулевые параметры.

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

Если подзапрос возвращает более одного значения, вам может потребоваться выполнить внешний запрос - если значения в столбце, указанном в условии, соответствуют любому значению в наборе результатов подзапроса. Для выполнения этой задачи необходимо использовать ключевое слово in.

Вы можете использовать подзапрос, чтобы проверить, существует ли набор записей. Для этого вам нужно использовать предложение exists с подзапросом. Ключевое слово exists всегда возвращает истинное или ложное значение.

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

Какой из них быстрее, зависит от количества запросов, полученных внутренним запросом:

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

СУЩЕСТВУЕТ оценивает истинность или ложь, но ВНУТР сравнивает несколько значений. Если вы не знаете, существует запись или нет, вам следует выбрать EXIST.

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

В этом разница:

select * 
from abcTable
where exists (select null)

Вышеуказанный запрос вернет все записи, а нижний - пустой.

select *
from abcTable
where abcTable_ID in (select null)

Попробуйте и посмотрите на результат.

Причина в том, что оператор EXISTS работает по принципу «хотя бы нашел». Он возвращает истину и останавливает сканирование таблицы после того, как будет найдена хотя бы одна соответствующая строка.

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

The general rule of thumb is that if the subquery contains a large volume of data, the EXISTS operator provides a better performance.

However, the query that uses the IN operator will perform faster if the result set returned from the subquery is very small.

Насколько я понимаю, оба должны быть одинаковыми, если мы не имеем дело со значениями NULL.

По той же причине, по которой запрос не возвращает значение для = NULL или равно NULL. http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

Что касается логического аргумента против аргумента компаратора, для генерации логического значения необходимо сравнить оба значения, и именно так работает любое условие if, поэтому я не могу понять, как IN и EXISTS ведут себя по-разному .

В поддерживает только отношения равенства (или неравенство, если ему предшествует НЕТ) .
Это синоним = любой / = некоторые, например

select    * 
from      t1 
where     x in (select x from t2)
;

СУЩЕСТВУЮТ поддерживает вариантные типы отношений, которые не могут быть выражены с помощью В, например. -

select    * 
from      t1 
where     exists (select    null 
                  from      t2 
                  where     t2.x=t1.x 
                        and t2.y>t1.y 
                        and t2.z like '℅' || t1.z || '℅'
                  )
;

И с другой стороны -

Предположительно производительность и технические различия между СУЩЕСТВУЮТ и В могут быть результатом реализаций / ограничений / ошибок конкретного поставщика, но во многих случаях они являются не чем иным, как мифами, созданными из-за непонимания внутреннего устройства баз данных.

Определение таблиц, точность статистики, конфигурация базы данных и версия оптимизатора - все это влияет на план выполнения и, следовательно, на показатели производительности.

Я считаю, что у этого есть простой ответ. Почему бы вам не проверить это у людей, которые разработали эту функцию в своих системах?

Если вы разработчик MS SQL, вот ответ непосредственно от Microsoft.

IN:

Determines whether a specified value matches any value in a subquery or a list.

EXISTS:

Specifies a subquery to test for the existence of rows.

Я обнаружил, что использование ключевого слова EXISTS часто очень медленное (это очень верно в Microsoft Access). Вместо этого я использую оператор соединения следующим образом: должен-я-использовать-ключевое-слово-существует-в-sql

In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS.

https://docs.oracle.com/cd/B19306_01/server.102/b14211/sql_1016.htm#i28403

В:

• Работает с набором результатов List
• Не работает с подзапросами, в результате получаются виртуальные таблицы с несколькими столбцами.
• Сравнивает каждое значение в списке результатов
• Производительность сравнительно МЕДЛЕННАЯ для большего набора результатов подзапроса

СУЩЕСТВУЮТ:

• Работает на виртуальных столах
• Используется со связанными запросами
• Выход из сравнения при нахождении совпадения
• Производительность сравнительно БЫСТРАЯ для большего набора результатов подзапроса