Использование переменной с оператором IN в T-SQL: синтаксис

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

Воспользуйтесь переменной таблицы для составления списка значений, управляемого переменной:

DECLARE @IDs TABLE (ID INT); -- Создаём список избранных
INSERT INTO @IDs VALUES (1), (2), (3); -- Добавляем значения

SELECT * FROM MyTable WHERE ID IN (SELECT ID FROM @IDs);

Оператор IN позволяет проверить, является ли элемент частью определенной группы, как например контроль гостей пропускного режима в VIP-зале.

УГЛУБЛЕНИЕ: Применение переменных таблиц

Создание переменной таблицы

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

DECLARE @Colors TABLE (ColorName NVARCHAR(50)); -- Цветные предпочтения 🎨
INSERT INTO @Colors VALUES ('Red'), ('Green'), ('Blue'); -- Заполняем список цветами

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

Использование оператора IN с переменными таблицами

Используйте переменную таблицы в подзапросах для фильтрации данных:

SELECT * FROM Products WHERE Color IN (SELECT ColorName FROM @Colors);

Обработка динамических списков

Функция STRING_SPLIT, доступная начиная с SQL Server 2016, упрощает работу с динамическими списками:

DECLARE @ColorList NVARCHAR(MAX) = 'Red,Green,Blue';
DECLARE @Colors TABLE (ColorName NVARCHAR(50));

INSERT INTO @Colors (ColorName)
SELECT VALUE FROM STRING_SPLIT(@ColorList, ',');

Сборка списков с помощью UNION ALL

Когда нужно на лету формировать списки в запросе SELECT из отдельных значений, используйте UNION ALL:

SELECT * FROM Products WHERE Color IN (
    SELECT 'Red'
    UNION ALL SELECT 'Green'
    UNION ALL SELECT 'Blue'
);

Визуализация

Можно представить оператор IN как входной список гостей на престижном мероприятии и переменную таблицы как список для проверки:

Эксклюзивное мероприятие 🎉
Список гостей [Оператор IN]: [👩‍💼, 👨‍🔬, 👩‍🚀, 👨‍🚒]

Переменная @FriendsToInvite:

Выбор гостей [Переменная]: [👩‍💼, 👨‍🚒]

Применим переменную @FriendsToInvite с оператором IN:

Кого приглашаем из друзей?
Результат: [👩‍💼, 👨‍🚒] ДОСТУП РАЗРЕШЕН 🎉!

Довольно живописное представление использования переменной с оператором IN в T-SQL, не правда ли?

ОСМОТР: Сложные практики и возможные трудности

Обработка неизвестных списков с помощью sp_executesql

Когда невозможно заранее узнать, какие значения будут использоваться...

DECLARE @SQL NVARCHAR(MAX), @Params NVARCHAR(MAX), @ValueList NVARCHAR(MAX) = '1,2,3';
SET @SQL = N'SELECT * FROM MyTable WHERE ID IN (SELECT VALUE FROM STRING_SPLIT(@pValueList, '',''))';
SET @Params = N'@pValueList NVARCHAR(MAX)';
EXEC sp_executesql @SQL, @Params, @pValueList = @ValueList;

Подход обладает большой гибкостью!

Будьте внимательны к соответствию типов данных

Внимательно следите за соответствием типов данных переменной и столбца в запросе:

-- Например:
DECLARE @ID INT = 123;
SELECT * FROM MyTable WHERE ID = @ID; -- Всё будет работать правильно

DECLARE @VarcharID VARCHAR(10) = '123';
SELECT * FROM MyTable WHERE ID = @VarcharID; -- Могут возникнуть неожиданности 🐰🎩

Производительность: переменные таблиц против временных таблиц

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

Чистый код с использованием CTE

Воспользуйтесь Общими Табличными Выражениями (CTE) для удобочитаемости кода:

WITH ValueList AS (
    SELECT 'Red' AS ColorName
    UNION ALL SELECT 'Green'
    UNION ALL SELECT 'Blue'
)
SELECT * FROM Products WHERE Color IN (SELECT ColorName FROM ValueList);

Код станет чище и аккуратнее, словно отглаженная рубашка.

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

1. IN (Transact-SQL) – SQL Server | Microsoft Learn — подробно о операторе IN в T-SQL.
2. Temporary Tables in SQL Server – Simple Talk — о временных таблицах: преимущества и лучшие практики.
3. Dynamic Search Conditions in T-SQL — обработка динамических условий поиска в T-SQL.