Три способа вычитания в языке SQL на простом примере

Структурированный язык запросов (SQL) во всех своих реализациях в первую очередь декларативен. Клиент (веб-сервер, клиентское приложение или та же среда разработки) дает серверу задачу, описывая, ЧТО он хочет получить, а уже оптимизатор запросов на сервере выбирает, КАК это сделать. Прелесть языка состоит в том, что в нем много избыточности и даже откровенного «синтаксического сахара» - все для сладкой жизни разработчиков. Многие конструкции языка введены для упрощения кода в стандартных и нестандартных ситуациях, и многие вещи, получается, можно выразить совершенно по-разному. От знания этих конструкций и умения ими пользоваться будет зависеть стиль кода, удобство его чтения и сопровождения, а в отдельных случаях и производительность работы системы, ведь разные «фразы» могут транслироваться в разные планы выполнения!

Рассмотрим несложный пример запроса на выборку информации из двух таблиц, связанных отношением «один-ко-многим». Каждому Клиенту соответствует 0, 1 или несколько Заказов, и некоторые Заказы были отменены. Требуется написать запрос, выдающий «хороших» Клиентов, чьи Заказы никогда не отменялись. Я использую в СУБД MS SQL Server, но в этих примерах Transact-SQL практически неотличим от стандарта SQL.

Client

Order

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

Favorite_Clients

Кстати, а КАК мы придем к такому результату? Ведь мы мыслим по-разному, и для такой задачки могут быть разные алгоритмы. Особенно, если строк будет побольше. В данном случае, мой продвинутый эвристический мозг сразу охватил всю таблицу Order, обнаружил, что выборка отмененных Заказов мала по сравнению с генеральной совокупностью, сохранил номера Клиентов из этих строк в «черный список», а потом механически скопировал таблицу Client и удалил из нее строки с номерами из «черного списка». Уф, писать это намного дольше, чем сделать! Зато сразу становится понятно, в каком направлении можно решать задачку на SQL: нам бы нужно вычесть что-то из всего множества строк таблицы Client. Итак, приступим.

«Если у вас есть только молоток, то все вокруг кажутся гвоздями»

Первое, чему обычно учатся в SQL, это JOIN (соединение таблиц), точнее INNER JOIN (внутреннее соединение). При виде отношения «один-ко-многим», он так и просится сюда

SELECT Client.id, Name, status
FROM Client INNER JOIN Order
ON Client.id = Order.id_client

Вот только что с этим дальше делать? WHERE, очевидно, не поможет, ведь у одного Клиента могут быть и успешные, и отмененные Заказы, да и GROUP BY с HAVING, на первый взгляд, не дают полезных ответов. А раз так, то и попытки использовать LEFT, RIGHT или FULL OUTER JOIN (внешние соединения) тоже лучше отложить.

«Черный список»

Зайдем с другой стороны, и запросим номера Клиентов, у которых Заказы отменялись

SELECT id_client FROM Order WHERE status = ‘Cancelled’

Вот эти плохиши:

Пора переходить к разным способам вычитания

Except и его недостатки

В реляционной алгебре вычитание относится к теоретико-множественным операциям, и напрямую реализовано инструкцией EXCEPT языка SQL

SELECT id FROM Client
EXCEPT
SELECT id_client FROM Order WHERE status = ‘Cancelled’

Очень просто, наглядно, читабельно и даже верно. Примерно так действует и человек! Вот только выражение SELECT до и после EXCEPT должны совпадать по количеству и порядку типов столбцов. Именно поэтому мы получаем только id наших Клиентов, а в постановке задачи подразумевались и имена тоже. Похоже, что без JOIN все-таки не обойтись:

SELECT Client.id, Name
FROM Client JOIN (
SELECT id FROM Client
EXCEPT
SELECT id_client FROM Order
WHERE status = ‘Cancelled’
) AS t ON Client.id=t.id

Ну вот, мне перестал нравиться этот запрос: тут и скобки, и аж 3 слова SELECT, и, главное, целых 2 раза сканируется таблица Client, а это угрожает производительности запроса.

Можно переписать покомпактнее

SELECT id, Name FROM Client
EXCEPT
SELECT Client.id, Name, status
FROM Client INNER JOIN Order
ON Client.id = Order.id_client
WHERE status = ‘Cancelled’

Но, скорее всего, производительность будет не лучше. Зависит от того, каких Клиентов больше – «хороших» или «плохих».

NOT IN – использование несвязанного вложенного запроса

К каждому значению в строке из некоего множества можно применить условие фильтрации IN или NOT IN, проверяющее наличие этого значение в другом множестве. В нашем случае, удобно написать:

SELECT id, Name FROM Client
WHERE id NOT IN (
SELECT id_client FROM Order
WHERE status = ‘Cancelled’ )

Заметим, что это практически идеальное решение и по производительности, и по читаемости/понятности для человека: «Выбрать таких Клиентов, чей id отсутствует в списке номеров клиентов отмененных заказов», или даже «Выбрать Клиентов, чей id отсутствует в Черном списке» (если пользоваться представлением).

NOT EXISTS – использование связанного вложенного запроса

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

SELECT Client.id, Name FROM Client
WHERE NOT EXISTS (
SELECT * FROM Order
WHERE status = ‘Cancelled’
AND Order.id_client = Client.id )

Очень полезный во многих задачках механизм, но здесь он смотрится чуточку громоздко и менее очевидно. А вот по производительности надо смотреть конкретные планы выполнения. Дело в том, что «как написано» он требует вложенного цикла по всем строкам таблицы Client с поиском в Order, но оптимизатор запросов, в зависимости от используемой СУБД, имеющихся индексов и конкретной статистики может выбрать более оптимальный вариант, например, идентичный предыдущему варианту с NOT IN.

Итак, мы рассмотрели 3 достаточно очевидных способа решения задачи, использующие чисто декларативные конструкции SQL. На практике могут возникнуть потребности сделать что-то похожее императивно – например с помощью серверного курсора, или даже реализовать в клиентском коде на императивном ЯВУ. У таких решений будут свои плюсы и минусы, рассмотрение которых выходит за рамки нашего краткого экскурса. Иногда причина использования императивного подхода – банальное незнание базовых, в общем, декларативных средств.

Рекомендуем:
Курс "Разработка реляционных баз данных в MS SQL Server. Язык запросов Transact-SQL"