Запросы использующие функцию exists. Предикат EXISTS SQL и проверка существования набора значений

WHERE EXISTS

Подзапрос проверяется на наличие одной или нескольких строк. Если хотя бы одна строка удовлетворяет запросу, то возвращается булево значение ИСТИНА. При указании дополнительного ключевого слова NOT булево значение ИСТИНА возвращается, если подзапрос не возвращает соответствующих ему строк.

подзапрос

На основе полностью сформированного подзапроса извлекается результирующий набор данных.

Общие правила

Оператор EXISTS проверяет существование одной или нескольких строк в подзапросе родительского запроса.

SELECT * FROM jobs WHERE NOT EXISTS (SELECT * FROM employee WHERE jobs.job_id=employye. job_id);

В этом примере проверяется в подзапросе записей с помощью дополнительного ключевого слова NOT. В следующем примере для извлечения основного результирующего набора данных производится поиск специфических записей в подзапросе.

SELECT au_lname FROM authors WHERE EXISTS (SELECT * FROM publishers WHERE authors.city=publishers.city);

Этот запрос возвращает фамилии авторов (au_lname), которые живут в том же городе, что и издатели (publishers). Обратите внимание, что можно использовать в подзапросе звездочку, поскольку подзапрос должен вернуть всего лишь одну запись с булевым значением ИСТИНА. В таких случаях столбцы не играют роли. Ключевой момент - это существование строки.

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

Оператор EXISTS семантически эквивалентен оператору ANY.

Подзапрос в операторе EXISTS обычно производит один из двух видов поиска. Первый вариант - это использование группового символа - звездочки (например, SELECT * FROM…), и в этом случае вы не извлекаете какой-то конкретный столбец или значение. Звездочка здесь означает «любой столбец». Второй вариант - выбор в подзапросе только одного конкретного столбца (например, SELECT aujd FROM). Некоторые отдельные платформы позволяют выполнять подзапросы по нескольким столбцам (например, SELECT aujd, aujname FROM…). Однако эта возможность достаточно редкая и ее следует избегать в коде, который нужно переносить на другие платформы.

Различия между платформами

Все платформы поддерживают оператор EXISTS в том виде, который мы описали выше.

«Раньше было проще» — Подумал я, садясь за оптимизацию очередного запроса в SQL management studio. Когда я писал под MySQL, реально все было проще — или работает, или нет. Или тормозит или нет. Explain решал все мои проблемы, больше ничего не требовалось. Сейчас у меня есть мощная среда разработки, отладки и оптимизации запросов и процедур/функций, и все это нагромождение создает по-моему только больше проблем. А все почему? Потому что встроенный оптимизатор запросов — зло. Если в MySQL и PostgreSQL я напишу

Select * from a, b, c where a.id = b.id, b.id = c.id

и в каждой из табличек будет хотя бы по 5к строк — все зависнет. И слава богу! Потому что иначе в разработчике, в лучшем случае, вырабатывается ленность писать правильно, а в худшем он вообще не понимает что делает! Ведь этот же запрос в MSSQL пройдет аналогично

Select * from a join b on a.id = b.id join c on b.id = c.id

Встроенный оптимизатор причешет быдлозапрос и все будет окей.

Он так же сам решит, что лучше делать — exist или join и еще много чего. И все будет работать максимально оптимально.

Только есть одно НО. В один прекрасный момент оптимизатор споткнется о сложный запрос и спасует, и тогда вы получите большущую проблему. И получите вы ее, возможно, не сразу, а когда вес таблиц достигнет критической массы.

Так вот к сути статьи. exists и in — очень тяжелые операции. Фактически это отдельный подзапрос для каждой строчки результата. А если еще и присутствует вложенность, то это вообще туши свет. Все будет окей, когда возвращается 1, 10, 50 строк. Вы не почувствуете разницы, а возможно join будет даже медленнее. Но когда вытаскивается 500 — начнутся проблемы. 500 подзапросов в рамках одного запроса — это серьезно.

Пусть с точки зрения человеческого понимания in и exists лучше, но с точки зрения временных затрат для запросов, возвращающих 50+ строк — они не допустимы.

Нужно оговориться, что естественно, если где-то убывает — где-то должно прибывать. Да, join более ресурсоемок по памяти, ведь держать единовременно всю таблицу значений и оперировать ею — накладнее, чем дергать подзапросы для каждой строки, быстро освобождая память. Нужно смотреть конкретно по запросу и замерять — критично ли будет использование лишней памяти в угоду времени или нет.

Приведу примеры полных аналогий. Вообще говоря, я не встречал еще запросов такой степени сложности, которые не могли бы быть раскручены в каскад join’ов. Пусть на это уйдет день, но все можно раскрыть.

Select * from a where a.id in (select id from b) select * from a where exists (select top 1 1 from b where b.id = a.id) select * from a join b on a.id = b.id select * from a where a.id not in (select id from b) select * from a where not exists (select top 1 1 from b where b.id = a.id) select * from a left join b on a.id = b.id where b.id is null

Повторюсь — данные примеры MSSQL оптимизатор оптимизирует под максимальную производительность и на таких простейших запросах тупняков не будет никогда.

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

Нужно вытащить все дубликаты «продуктов» в разных аккаунтах, ориентируясь на параметры продукта, его группы, и группы-родителя, если таковая есть.

Select d.PRODUCT_ID from PRODUCT s, PRODUCT_GROUP sg left join M_PG_DEPENDENCY sd on (sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID), PRODUCT d, PRODUCT_GROUP dg left join M_PG_DEPENDENCY dd on (dg.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID) where s.PRODUCT_GROUP_ID=sg.PRODUCT_GROUP_ID and d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID and sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC and sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME and s.PRODUCT_NAME=d.PRODUCT_NAME and s.PRODUCT_TYPE=d.PRODUCT_TYPE and s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE and s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT and dg.PRODUCT_GROUP_IS_TMPL=0 and ((sd.M_PG_DEPENDENCY_CHILD_ID is null and dd.M_PG_DEPENDENCY_CHILD_ID is null) or exists (select 1 from PRODUCT_GROUP sg1, PRODUCT_GROUP dg1 where sd.M_PG_DEPENDENCY_PARENT_ID = sg1.PRODUCT_GROUP_ID and dd.M_PG_DEPENDENCY_PARENT_ID = dg1.PRODUCT_GROUP_ID and sg1.PRODUCT_GROUP_PERSPEC=dg1.PRODUCT_GROUP_PERSPEC and sg1.PRODUCT_GROUP_NAME=dg1.PRODUCT_GROUP_NAME and))

Так вот это тот случай, когда оптимизатор спасовал. И для каждой строчки выполнялся тяжеленный exists, что убивало базу.

Select d.PRODUCT_ID from PRODUCT s join PRODUCT d on s.PRODUCT_TYPE=d.PRODUCT_TYPE and s.PRODUCT_NAME=d.PRODUCT_NAME and s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE and s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT join PRODUCT_GROUP sg on s.PRODUCT_GROUP_ID=sg.PRODUCT_GROUP_ID join PRODUCT_GROUP dg on d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID and sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME and sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC left join M_PG_DEPENDENCY sd on sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID left join M_PG_DEPENDENCY dd on dg.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID left join PRODUCT_GROUP sgp on sgp.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_PARENT_ID left join PRODUCT_GROUP dgp on dgp.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_PARENT_ID and sgp.PRODUCT_GROUP_NAME = dgp.PRODUCT_GROUP_NAME and isnull(sgp.PRODUCT_GROUP_IS_TMPL, 0) = isnull(dgp.PRODUCT_GROUP_IS_TMPL, 0) where (sd.M_PG_DEPENDENCY_CHILD_ID is null and dd.M_PG_DEPENDENCY_CHILD_ID is null) or (sgp.PRODUCT_GROUP_NAME is not null and dgp.PRODUCT_GROUP_NAME is not null) go

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

Это наглядный пример того, как доверие MSSQL оптимизатору может сыграть злую шутку. Не доверяйте ему, не ленитесь, join’те ручками, каждый раз думайте что лучше в данной ситуации — exists, in или join.



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

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

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

    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"

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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 могут возникать из-за специфических реализаций / ограничений / ошибок конкретного поставщика, но во многих случаях это ничего, кроме мифов, созданных из-за недостаточного понимания внутренних баз баз данных.

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

Предикат языка SQL EXISTS выполняет логическую задачу. В запросах SQL этот предикат используется в выражениях вида

EXISTS (SELECT * FROM ИМЯ_ТАБЛИЦЫ ...).

Это выражение возвращает истину, когда по запросу найдена одна или более строк, соответствующих условию, и ложь, когда не найдено ни одной строки.

Для NOT EXISTS всё наоборот. Выражение

NOT EXISTS (SELECT * FROM ИМЯ_ТАБЛИЦЫ ...)

возвращает истину, когда по запросу не найдено ни одной строки, и ложь, когда найдена хотя бы одна строка.

Наиболее простые запросы с предикатом SQL EXISTS

В примерах работаем с базой данных библиотеки и ее таблицами "Книга в пользовании" (BOOKINUSE) и "Пользователь" (USER). Пока нам потребуется лишь таблица "Книга в пользовании" (BOOKINUSE).

Author Title Pubyear Inv_No User_ID
Толстой Война и мир 2005 28 65
Чехов Вишневый сад 2000 17 31
Чехов Избранные рассказы 2011 19 120
Чехов Вишневый сад 1991 5 65
Ильф и Петров Двенадцать стульев 1985 3 31
Маяковский Поэмы 1983 2 120
Пастернак Доктор Живаго 2006 69 120
Толстой Воскресенье 2006 77 47
Толстой Анна Каренина 1989 7 205
Пушкин Капитанская дочка 2004 25 47
Гоголь Пьесы 2007 81 47
Чехов Избранные рассказы 1987 4 205
Пастернак Избранное 2000 137 18

Пример 1. Определить ID пользователей, которым выданы книги Толстого, которым также выданы книги Чехова. Во внешнем запросе отбираются данные о пользователях, которым выданы книги Толстого, а предикат EXISTS задаёт дополнительное условие, которое проверяется в во внутреннем запросе - пользователи, которым выданы книги Чехова. Дополнительным условием во внутреннем запросе является совпадение идентификаторов пользователей из внешнего и внутреннего запросов: User_ID=tols_user.user_id. Запрос будет следующим:

Этот запрос вернёт следующий результат:

Различия предикатов EXISTS и IN

При первом взгляде на запросы с предикатом EXISTS может возникнуть впечатление, что он идентичен предикату IN . Это не так. Хотя они очень похожи. Предикат IN ведет поиск значений из диапазона, заданного в его аргументе, и если такие значения есть, то выбираются все строки, соответствующие этому диапазону. Результат же действия предиката EXISTS представляет собой ответ "да" или "нет" на вопрос о том, есть ли вообще какие-либо значения, соответствующие указанным в аргументе. Кроме того, перед предикатом IN указывается имя столбца, по которому следует искать строки, соответствующие значениям в диапазоне. Разберём пример, показывающий отличие предиката EXISTS от предиката IN, и задачу, решаемую с помощью предиката IN.

Пример 4. Определить ID пользователей, которым выданы книги авторов, книги которых выданы пользователю с ID 31. Запрос будет следующим:

User_ID
120
65
205

Внутренний запрос (после IN) выбирает авторов: Чехов; Ильф и Петров. Внешний запрос выбирает всех пользователей, которым выданы книги этих авторов. Видим, что, в отличие от предиката EXISTS, предикат IN предваряется именем столбца, в данном случае - Author.

Запросы с предикатом EXISTS и дополнительными условиями

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

Пример 5. Определить ID пользователей, которым выдана хотя бы одна книга Пастернака, и которым при этом выдано более 2 книг. Пишем следующий запрос, в котором первое условие задаётся предикатом EXISTS со вложенным запросом, а второе условие с оператором HAVING всегда должно следовать после вложенного запроса:

Результат выполнения запроса:

User_ID
120

Как видно из таблицы BOOKINUSE, книга Пастернака выдана также пользователю с ID 18, но ему выдана всего одна книга и он не попадает в выборку. Если применить к подобному запросу ещё раз функцию COUNT, но уже для подсчёта выбранных строк (потренируйтесь в этом самостоятельно), то можно получить сведения о том, сколько пользователей, читающих книги Пастернака, при этом читают также книги других авторов. Это уже из сферы анализа данных.

Запросы с предикатом EXISTS к двум таблицам

Запросы с предикатом EXISTS могут извлекать данные из более чем одной таблицы. Многие задачи можно с тем же результатом решить с помощью оператора JOIN , но в ряде случаев использование EXISTS позволяет составить менее громоздкий запрос. Использовать EXISTS предпочительнее в тех случаях, когда в результирующую таблицу попадут столбцы лишь из одной таблицы.

В следующем примере из той же базы данных помимо таблицы BOOKINUSE потребуется также таблица "Пользователь" (USER).

Результатом выполнения запроса будет следующая таблица:

Author
Чехов
Маяковский
Пастернак

Как и в случае использования оператора JOIN, в случаях более одной таблицы следует использовать псевдонимы таблиц для проверки соответствия значений ключей, соединяющих таблицы. В нашем примере псевдонимы таблиц - bk и us, а ключ, соединяющий таблицы - User_ID.

Предикат EXISTS в соединениях более двух таблиц

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

Работаем с базой данных "Недвижимость". Таблица Deal содержит данные о сделках. Для наших заданий в этой таблице будет важен столбец Type с данными о типе сделки - продажа или аренда. Таблица Object содержит данные об объектах. В этой таблице нам понадобятся значения столбцов Rooms (число комнат) и LogBalc, содержащего данные о наличии лоджии или балкона в булевом формате: 1 (да) или 0 (нет). Таблицы Client, Manager и Owner содержат данные соответственно о клиентах, менеджерах фирмы и собственниках объектов недвижимости. В этих таблицах FName и LName соответственно имя и фамилия.

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

Так как из таблицы Client столбцы выбираются при помощи оператора "звёздочка", то будут выведены все столбцы этой таблицы, в которой будет столько строк, сколько насчитывается клиентов, соответствующих условию, заданному предикатом EXISTS. Из таблиц, к соединению которых обращается вложенный запрос, нам не требуется выводить ни одного столбца. Поэтому для экономии машинного времени извлекается лишь один столбец. Для этого после слова SELECT прописана единица. Этот же приём применён и в запросах в следующих примерах.

Написать запрос SQL с предикатом EXISTS самостоятельно, а затем посмотреть решение

Продолжаем писать вместе запросы SQL с предикатом EXISTS

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

Как и в предыдущем примере, из таблицы, к которой обращён внешний запрос, будут выведены все поля.

Пример 10. Определить число собственников, с объектами которых провёл менеджер Савельев. Пишем запрос, в котором внешний запрос обращается к соединению трёх таблиц, а предикатом EXISTS задано обращение лишь к одной таблице:

Все запросы проверены на существующей базе данных. Успешного использования!

Реляционные базы данных и язык SQL