Разность запросов — Множественные операции в SQL

• Оператор EXCEPT
  • Правила использования
• EXCEPT ALL
• Выводы

Представим, что нам нужно найти курсы, которые читались в 2022 году, но не читаются в 2023. Другими словами, нам нужно исключить из курсов 2022-го года курсы 2023-го. В языке SQL это можо сделать с помощью оператора EXCEPT.

Оператор EXCEPT

Чтобы из строк первого запроса исключить строки второго запроса, нужно между ними написать оператор EXCEPT:

SELECT coursename FROM course_members_2022

EXCEPT

SELECT coursename FROM course_members_2023;

Такой запрос вернет нам только одну строку: | coursename | |------------| | Ruby |

View on DB Fiddle

Правила использования

Для EXCEPT действуют те же два правила, что и для UNION и INTERSECT. В обоих подзапросах должно быть одинаковое количество столбцов, а сами столбцы должны иметь одинаковые типы данных.

Два следующих запроса не выполнятся, потому что в первом разное количество столбцов в подзапросах, а во втором - столбцы разного типа

-- В первом подзапросе два столбца, а во втором один
SELECT coursename FROM course_members_2022

EXCEPT

SELECT
    coursename,
    created_at
FROM course_members_2023;

-- Столбцы двух подзапросов имеют разные типы данных - в первом текст, а во втором - дата.
SELECT coursename FROM course_members_2022

EXCEPT

SELECT created_at FROM course_members_2023;

Для EXCEPT, как и для UNION и INTERSECT, СУБД будет следить только за тем, чтобы типы данных столбцов были одинаковые. Логический смысл СУБД не проверяет. Если мы в первом подзапросе укажем столбец coursename, а во втором - username, то запрос выполнится, но из первого подзапроса не будет исключено ни одной строки, потому что в нем нет имен пользователей.

-- Этот запрос выполнится, но из первого подзапроса не будет исключено ни одной строки
SELECT coursename FROM course_members_2022

EXCEPT

SELECT username FROM course_members_2023;

View on DB Fiddle

EXCEPT ALL

EXCEPT работает так: берутся все уникальные строки первого подзапроса и из них удаляются уникальные строки второго подзапроса. Бывают ситуации, когда сравнивать подзапросы нужно построчно, а не по уникальным записям. Например, мы не просто хотим узнать, какие курсы перестали читаться в 2023 году, а хотим понять, насколько менее популярным стал каждый из курсов. На помощь придет оператор EXCEPT ALL. Он работает так: берутся все строки первого подзапроса и из них по одной удаляются совпадающие строки второго подзапроса.

Рассмотрим на примере:

SELECT coursename FROM course_members_2022

EXCEPT ALL

SELECT coursename FROM course_members_2023;

Результат запроса:

View on DB Fiddle

Ruby выведено два раза, потому что в 2022 году этот курс прослушали два студента, а в 2023 году на курс никто не записывался.

PHP выведено три раза, потому что в 2022 году этот курс прослушали четыре студента, а в 2023 году один. Поэтому из четырех строк первого подзапроса была исключена одна строка второго.

Javascript не попал в результаты, потому что и в 2022 году, и в 2023 этот курс прослушали три человека. Поэтому из трех строк первого мы исключили три строки второго. Результат бы не изменился, даже если в 2023 году этот курс прослушали бы более трех человек.

Таким образом, можно сделать вывод, что Ruby и PHP стали менее интересны студентам, а к остальным курсам интерес студентов, как минимум, не пропал.

Выводы

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

EXCEPT выбирает уникальные строки первого подзапроса и исключает из них уникальные строки второго. EXCEPT ALL исключает строки по одной.

Дополнительные материалы

1. Сочетание запросов (UNION, INTERSECT, EXCEPT)
2. Union