Сравнение списка id с реляционной таблицей в SQL

Быстрый ответ

Чтобы отфильтровать определённые записи с помощью левого соединения и временной таблицы, можно применить комбинацию общих табличных выражений (Common Table Expressions, CTE) вместе с оператором VALUES:

-- Применяем CTE для исключения нежелательных Id
WITH ExcludeThese AS ( 
    SELECT * FROM (VALUES (1), (2), (3)) AS TempTable(Id) -- Создаём временный фильтр
)
SELECT m.*
FROM MainTable m
LEFT JOIN ExcludeThese ON m.Id = ExcludeThese.Id -- Производим соединение с основной таблицей
WHERE ExcludeThese.Id IS NULL; -- Исключаем отфильтрованные Id

Этот SQL-запрос позволит отсеить записи из MainTable с идентификаторами 1, 2, 3, создав временный набор данных прямо внутри запроса.

Разбор "магии": конструктор таблицы значений в SQL Server

Что же происходит внутри запроса? Ключевым элементом является оператор VALUES, который в SQL Server (начиная с версии 2008) используется в контексте конструктора таблицы значений. Он позволяет создать временную таблицу "на лету" и сразу же заполнить её несколькими строками одной командой.

Когда стоит использовать временные таблицы?

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

Понимание SQL-соединений на примерах из реальной жизни

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

Путь А (🛤️): [Поезд 1, Поезд 2, Поезд 3]
Путь Б (🛤️): [Поезд 2, Поезд 3, Поезд 4]

Вам нужно определить, какие поезда из пути А отсутствуют на пути Б.

🛤️А *(Левое Соединение)* 🛤️Б: [Поезд 1]

С помощью эксклюзивного левого соединения вы выясните, что поезд 1 уникален для пути А и не присутствует на пути Б.

🚩 [Поезд 1] – уникальный поезд, не встречающийся на пути Б.

Относительно нашего запроса, временная таблица ExcludeThese служит фильтром, который SQL движок использует для выделения и исключения уникальных идентификаторов (в нашем случае – поездов 🚂) из MainTable.

Стратегии оптимизации SQL-запросов

Основное мастерство в составлении эффективных SQL-запросов заключается в грамотном их балансировании и стратегическом подходе к их написанию. Вот несколько советов по этой теме:

Подробнее об этом расскажет наш спикер на видео

Использование первичных ключей и индексов

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

Чёткая структура

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

Избегание дубликатов

Если вы используете UNION ALL для объединения результатов, помните, что он не удаляет дубликаты. Чтобы гарантировать уникальность ID, используйте UNION для удаления дубликатов или убедитесь, что данные в VALUES уникальны.

Удаление временных таблиц

Если вы создаёте временные таблицы вместо CTE, обязательно проводите очистку после использования с помощью команды DROP TABLE.

Тестирование и демонстрация результатов

Экспериментируйте и тестируйте свои SQL-запросы, используя, например, сервис SQLFiddle, который позволяет выполнить ваш запрос и увидеть результаты в изолированной среде.

Полезные материалы

1. MySQL :: Руководство по MySQL 8.0 :: 15.2.15.8 Производные таблицы — Обзор использования производных таблиц в MySQL.
2. SQL VALUES: Создание строк на основе заданных выражений для столбцов — Глубокое погружение в возможности оператора VALUES в SQL.
3. sql – генерация дней из диапазона дат – Stack Overflow — Практические примеры создания запросов с динамическими диапазонами дат.
4. Временные таблицы в SQL Server – Simple Talk — Руководство по эффективному использованию временных таблиц в SQL Server.
5. PostgreSQL: Документация: 16: 7.2. Табличные выражения — Исследование особенностей табличных выражений в PostgreSQL для сложных запросов.