Базы данных
и язык SQL
Форум
Тесты онлайн

Предикат EXISTS SQL и проверка существования набора значений

Навигация по уроку Связанные темы
Назад<<<СодержаниеВперёд>>>

Назначение предиката SQL EXISTS

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

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

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

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

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

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

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

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке.

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

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

Скрипт для создания базы данных библиотеки, её таблиц и заполения таблиц данными - в файле по этой ссылке.

AuthorTitlePubyearInv_NoCustomer_ID
ТолстойВойна и мир20052865
ЧеховВишневый сад20001731
ЧеховИзбранные рассказы201119120
ЧеховВишневый сад1991565
Ильф и ПетровДвенадцать стульев1985331
МаяковскийПоэмы19832120
ПастернакДоктор Живаго200669120
ТолстойВоскресенье20067747
ТолстойАнна Каренина19897205
ПушкинКапитанская дочка20042547
ГогольПьесы20078147
ЧеховИзбранные рассказы19874205
ПушкинСочинения, т.11984647
ПастернакИзбранное200013718
ПушкинСочинения, т.219848205
NULLНаука и жизнь 9 2018201912718
ЧеховРанние рассказы200117131

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

SELECT Customer_ID FROM Bookinuse AS tols_user WHERE Author='Толстой' AND EXISTS (SELECT Customer_ID FROM Bookinuse WHERE Author='Чехов' AND Customer_ID=tols_user.Customer_id)

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

Customer_ID
65
205

Далее - пример использования NOT EXISTS в запросе, решающем похожую задачу.

Пример 2. Определить ID пользователей, которым выданы книги Чехова, и которым при этом не выданы книги Ильфа и Петрова. Конструкция запроса аналогична конструкции из предыдущего примера с той разницей, что дополнительное условие задаётся предикатом NOT EXISTS. Запрос будет следующим:

SELECT Customer_ID FROM Bookinuse AS cheh_user WHERE Author='Чехов' AND NOT EXISTS (SELECT Customer_ID FROM Bookinuse WHERE Author='Ильф и Петров' AND Customer_ID=cheh_user.Customer_id)

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

User_ID
120
65
205

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

Пример 3. Определить автора (авторов), книги которого выданы пользователю с ID 120, а также с ID 18.

Правильное решение и ответ.

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

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

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

SELECT Customer_ID FROM Bookinuse WHERE Author IN (SELECT Author FROM Bookinuse WHERE Customer_ID=31)

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

User_ID
120
65
205

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

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

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

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

SELECT Customer_ID FROM Bookinuse AS pas_user WHERE EXISTS (SELECT Customer_ID FROM Bookinuse WHERE Author='Пастернак' AND Customer_ID=pas_user.Customer_ID) GROUP BY Customer_ID HAVING COUNT(Title) > 2

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

User_ID
120

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

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

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

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

Customer_IDSurname
18Зотов
31Перов
47Васин
65Тихонов
120Краснов
205Климов

Пример 6. Определить авторов, книги которых выданы пользователю по фамилии Краснов. Пишем следующий запрос, в котором предикатом EXISTS задано единственное условие:

SELECT DISTINCT Author FROM Bookinuse bk WHERE EXISTS (SELECT * FROM Customer cs WHERE cs.Customer_ID=bk.Customer_ID AND Surname='Краснов')

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

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

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

Примеры запросов к базе данных "Библиотека" есть также в уроках по операторам GROUP BY, IN и функциям CONCAT, COALESCE.

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

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

Работаем с базой данных "Недвижимость". Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными - в файле по этой ссылке.

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

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

SELECT cl.* FROM Client cl WHERE EXISTS (SELECT 1 FROM Deal de JOIN Object ob ON ob.Obj_ID=de.Object_ID WHERE de.Client_ID=cl.Client_ID AND ob.LogBalc=0)

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

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

Пример 3. Определить менеджеров, которые провели сделки с объектами с числом комнат больше 2.

Правильное решение.

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

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

SELECT ow.* FROM Owner ow WHERE EXISTS (SELECT 1 FROM Object ob JOIN Deal de ON de.Object_ID=ob.Obj_ID WHERE ow.Owner_ID=ob.Owner_ID AND de.Type='rent')

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

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

SELECT COUNT(*) FROM Object ob JOIN Deal de ON de.Object_ID=ob.Obj_ID JOIN Owner ow ON ob.Owner_ID=ow.Owner_ID WHERE EXISTS (SELECT 1 FROM Manager ma WHERE de.Manager_ID=ma.Manager_ID AND ma.LName='Савельев')

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

Примеры запросов к базе данных "Недвижимость" есть также в уроках по операторам GROUP BY и IN.

Поделиться с друзьями

Назад<<<СодержаниеВперёд>>>