SQL Interview Prep · Урок

Имитация операций над множествами с помощью JOIN

Переписывайте EXCEPT и INTERSECT в диалектах, где их нет.

«Имитация операций над множествами с помощью JOIN» — бесплатный урок SQL Interview Prep на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Interview Prep содержит 4 уроков всего.

Зачем воспроизводить операции над множествами

Не каждая база данных поддерживает INTERSECT и EXCEPT. Например, в старых версиях MySQL они полностью отсутствовали. На собеседованиях проверяют, можете ли вы воспроизвести логику операций над множествами с помощью соединений и вложенных запросов, если оператор недоступен.

Знание как самого оператора над множествами, так и его эквивалента через соединение доказывает, что вы понимаете, какие вычисления он выполняет.

INTERSECT как INNER JOIN

INTERSECT находит строки, общие для обоих множеств. Эквивалент через соединение — это INNER JOIN по всем сравниваемым столбцам плюс DISTINCT, чтобы воспроизвести удаление дубликатов.

Каждый столбец, участвующий в сравнении, становится частью условия соединения.

-- A INTERSECT B emulated:
SELECT DISTINCT a.customer_id
FROM orders_2023 a
JOIN orders_2024 b
  ON a.customer_id = b.customer_id;

Зачем нужен DISTINCT для INTERSECT

Обычный INNER JOIN может размножать строки: если значение встречается несколько раз с любой стороны, соединение создаёт несколько строк. Стандартный INTERSECT возвращает каждую общую строку один раз, поэтому добавьте DISTINCT, чтобы убрать дубликаты, созданные соединением.

Забыть DISTINCT в этом случае — распространённая ошибка на собеседовании.

-- without DISTINCT, a customer with 3 orders in each year
-- would appear 9 times from the join

EXCEPT в конструкции LEFT JOIN / IS NULL

EXCEPT (A, но не B) — это антисоединение. Переносимый вариант — выполнить LEFT JOIN от A к B по всем столбцам, оставить только строки, в которых сторона B содержит NULL (совпадение не найдено), а затем применить DISTINCT.

Шаблон LEFT JOIN / IS NULL — один из самых часто используемых приёмов на собеседованиях по SQL.

SELECT DISTINCT a.customer_id
FROM orders_2023 a
LEFT JOIN orders_2024 b
  ON a.customer_id = b.customer_id
WHERE b.customer_id IS NULL;

EXCEPT с NOT EXISTS

Столь же переносимый вариант EXCEPT использует NOT EXISTS. Его смысл: «оставить каждую строку A, для которой не существует совпадающей строки B». Такой вариант надёжно обрабатывает значения NULL.

Многие разработчики предпочитают NOT EXISTS, поскольку его назначение очевидно и он позволяет избежать ловушки NOT IN + NULL.

SELECT DISTINCT a.customer_id
FROM orders_2023 a
WHERE NOT EXISTS (
  SELECT 1 FROM orders_2024 b
  WHERE b.customer_id = a.customer_id
);

INTERSECT с EXISTS

Аналогично, INTERSECT можно записать с помощью EXISTS: оставить каждую уникальную строку A, для которой существует совпадающая строка B.

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

SELECT DISTINCT a.customer_id
FROM orders_2023 a
WHERE EXISTS (
  SELECT 1 FROM orders_2024 b
  WHERE b.customer_id = a.customer_id
);

Ловушка NOT IN с NULL

Соблазнительный способ имитировать EXCEPT — использовать NOT IN, но это опасно: если подзапрос возвращает хотя бы один NULL, NOT IN не возвращает ни одной строки, поскольку результат сравнения становится UNKNOWN.

Это ловушка, которую часто проверяют. Предпочитайте NOT EXISTS или LEFT JOIN / IS NULL — они корректно работают со значениями NULL.

-- RISKY if orders_2024.customer_id can be NULL:
SELECT DISTINCT customer_id FROM orders_2023
WHERE customer_id NOT IN (
  SELECT customer_id FROM orders_2024
);

Сопоставление по нескольким столбцам

Если сравнение множеств охватывает несколько столбцов, каждый столбец должен участвовать в предикате соединения. Для антисоединения необходимо также учитывать возможность наличия NULL в этих столбцах — именно здесь особенно полезен NOT EXISTS.

Явно укажите каждый столбец в предложении ON: если пропустить хотя бы один, смысл понятия «равная строка» незаметно изменится.

SELECT DISTINCT a.id, a.city
FROM a
LEFT JOIN b
  ON a.id = b.id AND a.city = b.city
WHERE b.id IS NULL;

Имитация UNION без оператора

UNION ALL — это обычная конкатенация результатов, которую каждый диалект поддерживает напрямую. Чтобы при необходимости имитировать UNION без дубликатов, выполните конкатенацию с помощью UNION ALL внутри подзапроса и оберните её в SELECT DISTINCT или сгруппируйте все столбцы с помощью GROUP BY.

Это показывает, что UNION — всего лишь UNION ALL с последующим удалением дубликатов.

SELECT DISTINCT * FROM (
  SELECT city FROM a
  UNION ALL
  SELECT city FROM b
) combined;

Выбор подходящего варианта

Руководство по выбору:

INTERSECT → EXISTS или INNER JOIN + DISTINCT.
EXCEPT → NOT EXISTS или LEFT JOIN / IS NULL.
Избегайте NOT IN, если возможны значения NULL.
UNION → UNION ALL, обёрнутый в DISTINCT.

EXISTS / NOT EXISTS наиболее переносимы и безопасны при наличии NULL, поэтому это самые надёжные ответы на собеседовании.

Связываем всё воедино

Умение преобразовывать операторы множеств в соединения показывает, что Вы понимаете их как логику множеств, а не просто как синтаксис. Антисоединение (LEFT JOIN / IS NULL или NOT EXISTS) — самый полезный шаблон: он встречается при имитации EXCEPT, поиске строк-сирот и решении задач о пропущенных записях.

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

Быстрая проверка

Ваша база данных не поддерживает EXCEPT. Вам нужно получить идентификаторы клиентов из заказов за 2023 год, которых нет среди заказов за 2024 год, причём столбец может содержать NULL.

Итоги

Главное:

INTERSECT → INNER JOIN + DISTINCT или EXISTS.
EXCEPT → LEFT JOIN / IS NULL или NOT EXISTS (антисоединение).
Добавляйте DISTINCT, чтобы соответствовать удалению дубликатов, выполняемому операторами множеств, и сдерживать размножение строк при соединении.
Избегайте NOT IN, если возможны NULL; предпочитайте NOT EXISTS.
UNION = UNION ALL, обёрнутый в DISTINCT.

Часто задаваемые вопросы

Урок «Имитация операций над множествами с помощью JOIN» бесплатный?

Да — полный текст урока «Имитация операций над множествами с помощью JOIN» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Interview Prep, подпишись на CoddyKit PRO. Курс SQL Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «Имитация операций над множествами с помощью JOIN»?

Переписывайте EXCEPT и INTERSECT в диалектах, где их нет. Ты практикуешь SQL Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Interview Prep?

Предыдущий опыт не требуется. SQL Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.

Сколько времени занимает урок «Имитация операций над множествами с помощью JOIN»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Interview Prep?

Да. Каждый урок SQL Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

UNION и UNION ALL
Совместимость количества и типов столбцов
INTERSECT и EXCEPT для сравнения
Имитация операций над множествами с помощью JOIN

← Назад к SQL Interview Prep