Аналогии между INTERSECT и EXISTS, EXCEPT и NOT EXISTS: более сложные примеры

Навигация по уроку

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

Напротив, оператор EXCEPT и его аналог - предикат NOT EXISTS - извлекает те записи, которые есть в результатах первого запроса, и которых нет среди результатов второго запроса.

Для того, чтобы сработали названные выше предикаты и операторы, в запросах должны совпадать число столбцов, порядок их следования и имена.

Работаем с базой данных Mediastar, схема и содержание которой даны здесь.

Скачать архив со скриптом на SQL для установки этой базы со всеми данными можно по этой ссылке.

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

Работаем с таблицами, показанными на рисунке ниже.

Пример 1. Вывести значения возраста всех пользователей, которые просматривали медиа (сайты) не только по теме "Обучение", но и по теме "Хобби". Использовать предикат EXISTS.

Задействованы все три таблицы, показанные на рисунке после вступления к уроку.

Запрос выполняет операцию пересечения множеств. Предикат EXISTS находится в секции WHERE в качестве второго условия после ключевого слова AND. Обратим особое внимание на то, что запросы с предикатом EXISTS - это коррелирующие запросы, то есть результат выполнения подзапроса зависит от результата выполнения основного запроса. Эта зависимость задаётся в подзапросе в секции WHERE в качестве второго условия: r2.Age=r1.Age, то есть значения возраста в запросе и подзапросе должны совпадать.

Запрос для выполнения этого задания должен быть таким:

USE mediastar; SELECT DISTINCT r1.Age FROM Mediatheme mt1 JOIN Media m1 ON m1.Mtheme_ID=mt1.Mtheme_ID JOIN Visit v1 ON v1.Media_ID=m1.Media_ID JOIN Reader r1 ON r1.Reader_ID=v1.Reader_ID WHERE mt1.Name='Обучение' AND EXISTS (SELECT DISTINCT r2.Age FROM Mediatheme mt2 JOIN Media m2 ON m2.Mtheme_ID=mt2.Mtheme_ID JOIN Visit v2 ON v2.Media_ID=m2.Media_ID JOIN Reader r2 ON r2.Reader_ID=v2.Reader_ID WHERE mt2.Name='Хобби' AND r2.Age=r1.Age)

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

Пример 2. Как и в предыдущем задании, вывести значения возраста всех пользователей, которые просматривали медиа (сайты) не только по теме "Обучение", но и по теме "Хобби". Использовать оператор INTERSECT.

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

Запрос для выполнения этого задания должен быть таким:

USE mediastar; SELECT DISTINCT r1.Age FROM Mediatheme mt1 JOIN Media m1 ON m1.Mtheme_ID=mt1.Mtheme_ID JOIN Visit v1 ON v1.Media_ID=m1.Media_ID JOIN Reader r1 ON r1.Reader_ID=v1.Reader_ID WHERE mt1.Name='Обучение' INTERSECT SELECT DISTINCT r2.Age FROM Mediatheme mt2 JOIN Media m2 ON m2.Mtheme_ID=mt2.Mtheme_ID JOIN Visit v2 ON v2.Media_ID=m2.Media_ID JOIN Reader r2 ON r2.Reader_ID=v2.Reader_ID WHERE mt2.Name='Хобби'

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

Задания для самостоятельного решения

Пример 3. Вывести значения возраста пользователей, которые просматривали медиа (сайты) по теме "Обучение", но никогда по теме "Хобби". Использовать оператор EXCEPT.

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

Пример 4. Вывести значения возраста пользователей, которые просматривали медиа (сайты) по теме "Обучение", но никогда по теме "Хобби". Использовать предикат NOT EXISTS.

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

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

Назад<<<

Содержание

Вперёд>>>