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

В чем разница между EXISTSи INпунктом в SQL?

Когда мы должны использовать EXISTS, и когда мы должны использовать IN?

441 sql
24.08.2008 08:42:23
20 ОТВЕТОВ

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

40
8.07.2016 13:51:28
Я не знаю об ОП, но мне хотелось бы получить ответ на этот вопрос! Когда я должен использовать EXISTS вместо IN с подзапросом, который возвращает идентификаторы?
Roy Tinker 13.07.2010 17:45:12
в JOIN, вам понадобитсяDISTINCT
Jaider 26.03.2014 18:51:11
отличная демонстрация, но в значительной степени оставьте вопрос без ответа
Junchen Liu 14.03.2017 17:00:03

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)
124
19.08.2017 06:27:09
Последний запрос опасен, потому что он может потерпеть неудачу в том случае, если подзапрос не возвращает никаких результатов. предложение 'in' требует как минимум 1 аргумент ...
user2054927 1.04.2016 09:26:53
@ user2054927 Последний запрос не будет возвращать ни одной строки, если подзапрос не возвращает ни одной строки - в этом нет ничего опасного!
Tony Andrews 4.06.2016 09:36:48
Лучший ответ.
Aminadav Glickshtein 4.07.2019 18:17:25

Думаю,

  • EXISTSэто когда вам нужно сопоставить результаты запроса с другим подзапросом. Результаты запроса # 1 должны быть получены там, где совпадают результаты SubQuery. Вид присоединения. Например, таблица клиентов № 1, которые также разместили таблицу заказов № 2

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

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

5
26.07.2011 19:21:45

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выражении есть таблица, имеет больше смысла использовать a join, но в основном это не должно иметь значения. Оптимизатор запросов должен возвращать тот же план в любом случае. В некоторых реализациях (в основном старых, таких как Microsoft SQL Server 2000) inзапросы всегда получают вложенный план соединения , тогда как joinзапросы будут использовать вложенные, слияния или хэширования в зависимости от ситуации. Более современные реализации умнее и могут корректировать план даже при inиспользовании.

222
6.08.2015 07:21:04
Не могли бы вы пояснить: «Когда у вас есть таблица в операторе in, имеет смысл использовать объединение, но это не имеет значения. Оптимизатор запросов в любом случае вернет тот же план».? Не часть оптимизатора запросов, а часть, где вы можете использовать ее JOINв качестве замены IN.
farthVader 27.01.2015 03:52:04
select * from [table] where [field] in (select [field] from [table2])возвращает те же результаты (и план запроса), что и select * from [table] join [table2] on [table2].[field] = [table].[field].
user4570983 5.08.2015 18:45:47
@ Сандер это не делает: первый запрос возвращает все столбцы из table, а второй возвращает все из tableи table2. В некоторых (в основном старых) базах данных SQL inзапрос будет реализован как вложенное объединение, в то время как joinзапрос может быть вложенным, объединенным, хэшированным и т. Д. - как угодно быстро.
Keith 5.08.2015 19:43:58
Хорошо, я должен был указать столбцы в предложении select, но вы должны обновить свой ответ, потому что в нем четко указано, что запросы «в любом случае будут возвращать один и тот же план».
user4570983 5.08.2015 20:07:51
existsможет использоваться внутри оператора case, поэтому они также могут быть полезны таким образом, например:select case when exists (select 1 from emp where salary > 1000) then 1 else 0 end as sal_over_1000
smooth_smoothie 25.08.2016 04:17:14

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

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

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

  • Нет никакой разницы.
80
18.08.2015 20:47:24
Доказательство вашего аргумента? Я не думаю, что IN будет быстрее, чем EXISTS!
Nawaz 6.05.2014 06:50:40
@Nawaz Как насчет доказательства, почему IN всегда медленнее, чем EXISTS?
ceving 10.07.2014 13:55:54
Плохо реализован оптимизатор запросов? Мне кажется, что нечто подобное (хотя и не совсем в такой ситуации) происходит в определенных RDBM ...
Haroldo_OK 19.08.2015 17:31:18
EXISTS возвращает чисто логические значения, что всегда быстрее, чем сравнение строк или значений, больших, чем тип BIT / Boolean. IN может или не может быть логическим сравнением. Поскольку программирование предпочитает использовать EXPLICIT для стабильности (часть ACID), EXISTS предпочтительнее вообще.
clifton_h 28.06.2016 06:48:00
Почему за это проголосовали так много раз? Нет абсолютно никакой причины, почему это основанное на предположениях утверждение должно быть в целом верным.
Lukas Eder 31.01.2018 21:37:04

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

3
21.03.2012 09:33:16

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

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

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

9
19.10.2012 21:30:22

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

-2
20.10.2012 12:09:25
@ Зигги, объясни? Это в значительной степени то, что говорит и принятый ответ. При ОБЯЗАТЕЛЬНО проверять каждую отдельную запись, существование может прекратиться, как только найдет только одну.
Ben Thurley 15.07.2014 14:54:53
Нет, не правильно. INи EXISTSможет быть эквивалентным и преобразованным друг в друга.
Lukas Eder 31.01.2018 21:42:16
  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 есть нулевое значение, тогда Query 1 найдет их, но Query 2 не может найти нулевые параметры.

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

28
4.07.2012 13:02:35
Этот ответ является разумным кратким изложением настроений Тома Кайта ( asktom.oracle.com/pls/asktom/… )
Jeromy French 6.09.2013 14:58:51
Я думаю, что этот ответ основан на интуиции, что достаточно справедливо. Но это не может быть универсально правдой. Например, почти наверняка это не так с Ingres , который будет анализировать оба эквивалентных SQL-запроса как один и тот же QUEL-запрос, которому не хватает «богатства» SQL, когда речь идет о написании одной и той же вещи несколькими способами.
onedaywhen 23.08.2016 07:56:13
Эти 2 запроса логически эквивалентны, если и только если t2.id определен как «NOT NULL». Чтобы получить эквивалентность без зависимости в определении таблицы, 2-й запрос должен быть «ВЫБРАТЬ t1. * ОТ Т1, ГДЕ t1.id не находится (ВЫБРАТЬ t2.id ОТ Т2, где t2.id не нуль
David דודו Markovitz 9.10.2016 09:50:38

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

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

0
4.12.2012 11:48:10

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

-1
9.04.2013 19:03:43
Это утверждение действительно не подкреплено какими-либо доказательствами, не так ли?
Lukas Eder 31.01.2018 21:40:56

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

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

EXIST оценивает на true или false, но IN сравнивает множественное значение. Если вы не знаете, существует запись или нет, вы должны выбрать EXIST

3
20.09.2013 15:17:45

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

16
16.07.2013 08:24:16

Разница заключается здесь:

select * 
from abcTable
where exists (select null)

Выше запрос вернет все записи, а ниже один вернется пустым.

select *
from abcTable
where abcTable_ID in (select null)

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

4
19.05.2015 12:42:56
Хммм ... Ошибка: [SQL0104] Token) был недействителен. В обоих случаях. Вы предполагаете конкретную СУРБД?
jmarkmurphy 10.08.2017 15:42:01

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

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

Общее правило состоит в том, что если подзапрос содержит большой объем данных, оператор EXISTS обеспечивает лучшую производительность.

Однако запрос, использующий оператор IN, будет выполняться быстрее, если набор результатов, возвращаемый из подзапроса, очень мал.

3
7.04.2016 09:04:10

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

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

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

1
12.09.2016 22:02:00

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

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

EXISTS поддерживает разные типы отношений, которые нельзя выразить с помощью IN , например:

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 || '℅'
                  )
;

И на другой ноте -

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

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

10
28.09.2017 19:58:35
Upvote за ваш комментарий о производительности: не сосредотачиваясь на конкретной СУБД, мы должны предположить, что оптимизатор должен решить, что работает лучше всего.
Manngo 10.11.2017 06:27:04

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

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

IN:

Определяет, соответствует ли указанное значение какому-либо значению в подзапросе или списке.

EXISTS:

Определяет подзапрос для проверки существования строк.

0
24.03.2017 05:27:29

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

0
10.07.2017 16:02:37

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

1
17.08.2017 14:10:53