IN: примеры (SQL)
IN(expression [NOT] IN subquery|expression_list): BooleanОператор IN в MS SQL Server
Оператор IN в Transact-SQL используется для сравнения значения с набором значений, заданным явно или полученным в результате подзапроса. Он возвращает TRUE, если проверяемое значение совпадает хотя бы с одним элементом из указанного списка или результата подзапроса.
Основные случаи применения оператора включают фильтрацию строк в условиях WHERE, проверку принадлежности значения к множеству в выражениях CASE и упрощение сложных конструкций с множественными условиями OR.
Синтаксис оператора имеет две основные формы:
expression IN (value1, value2, ..., valueN) expression IN (subquery)
Аргументы:
- expression - любое допустимое выражение SQL, которое сравнивается с набором.
- value1, ... valueN - список констант, выражений или NULL, разделенных запятыми. Все значения должны иметь тип, совместимый с expression, либо допускать неявное преобразование.
- subquery - подзапрос, возвращающий один столбец. Тип данных этого столбца также должен быть совместим с expression.
Возвращаемое значение - логическое (BOOLEAN): TRUE, FALSE или UNKNOWN (в случае сравнения с NULL).
Использование оператора NOT IN инвертирует результат: возвращает TRUE, если значение не найдено в указанном наборе.
Простые примеры использования оператора IN
Пример с явным перечислением строковых значений:
SELECT ProductID, Name
FROM Production.Product
WHERE Name IN ('Chainring', 'Crankarm', 'Touring-3000');ProductID Name ----------- -------------------- 320 Chainring 316 Crankarm 841 Touring-3000
Пример с числовыми значениями:
SELECT SalesOrderID, TotalDue
FROM Sales.SalesOrderHeader
WHERE TotalDue IN (250.00, 500.00, 1000.00);SalesOrderID TotalDue ------------ --------------------- 43659 250.00 43660 500.00 43661 1000.00
Использование NOT IN:
SELECT ProductID, Name
FROM Production.Product
WHERE ProductID NOT IN (1, 2, 3, 4);ProductID Name ----------- -------------------- 5 HL Mountain Frame 6 HL Road Frame ... ...
Пример с подзапросом:
SELECT Name
FROM Production.Product
WHERE ProductSubcategoryID IN (
SELECT ProductSubcategoryID
FROM Production.ProductSubcategory
WHERE Name LIKE '%Bike%'
);Name ---------------------------- Mountain-100 Silver Mountain-100 Black Road-150 Red
Альтернативные методы фильтрации в MS SQL
EXISTS - используется с коррелированными подзапросами. Часто оказывается эффективнее IN при работе с большими наборами данных, так как проверяет наличие хотя бы одной строки и может раньше завершить поиск. Пример:
SELECT Name
FROM Production.Product p
WHERE EXISTS (
SELECT 1
FROM Production.ProductSubcategory ps
WHERE p.ProductSubcategoryID = ps.ProductSubcategoryID
AND ps.Name LIKE '%Bike%'
);JOIN - явное соединение таблиц. Предпочтительнее, когда необходимы данные из связанных таблиц. Пример:
SELECT p.Name
FROM Production.Product p
INNER JOIN Production.ProductSubcategory ps
ON p.ProductSubcategoryID = ps.ProductSubcategoryID
WHERE ps.Name LIKE '%Bike%';Множественные условия OR - прямая альтернатива для небольших списков. Оператор IN улучшает читаемость по сравнению с длинными цепочками OR.
Табличные выражения (VALUES) - позволяют создать временную таблицу на лету. Полезно для передачи большого набора констант.
SELECT ProductID, Name
FROM Production.Product
WHERE ProductID IN (
SELECT value
FROM (VALUES (1), (2), (3), (4)) AS t(value)
);Оператор IN в других СУБД и языках
MySQL: синтаксис аналогичен, но есть оптимизация для подзапросов. Пример:
SELECT * FROM users WHERE id IN (1, 5, 10);Oracle: поддерживает расширенный синтаксис для сравнения нескольких столбцов. Пример:
SELECT * FROM emp WHERE (deptno, job) IN ((10, 'CLERK'), (20, 'MANAGER'));PostgreSQL: поддерживает те же возможности, что и Oracle, и дополнительно позволяет использовать IN с конструкцией ANY.
SELECT * FROM products WHERE category_id = ANY(ARRAY[1, 2, 3]);SQLite: стандартная реализация, но с ограничениями по количеству параметров в некоторых версиях.
Sybase ASE: поведение аналогично MS SQL, так как они имеют общие корни.
Языки программирования: концепция проверки принадлежности к множеству реализована в коллекциях. Например, в Python: if item in [1, 2, 3]:, в C#: if (array.Contains(value)).
Распространенные ошибки при использовании IN
Неявное преобразование типов может привести к неожиданным результатам или ошибкам.
DECLARE @id varchar(10) = '10';
SELECT * FROM Orders WHERE OrderID IN (@id, 20, 30);
-- OrderID имеет тип int. Сравнение '10' и 10 может работать, но неэффективно.NULL в списке значений с NOT IN приводит к тому, что результат всего условия становится UNKNOWN, и строки не возвращаются.
SELECT 'Found' WHERE 1 NOT IN (2, NULL, 3);
-- Результат: пустой набор, так как 1 != NULL дает UNKNOWN.Одинаковые значения в списке не являются ошибкой, но могут замедлить выполнение.
SELECT * FROM t WHERE id IN (1, 1, 1);
-- Дубликаты игнорируются, но лучше их избегать.Подзапрос возвращает несколько столбцов приводит к синтаксической ошибке.
SELECT * FROM t1 WHERE col IN (SELECT col1, col2 FROM t2);
-- Ошибка: Only one expression can be specified.Большой список значений может превысить ограничение на длину запроса или производительность.
Изменения в работе оператора IN в современных версиях SQL Server
В SQL Server 2019 и более новых версиях интеллектуальная обработка запросов (Intelligent Query Processing) может улучшать производительность запросов с IN, особенно в сочетании с подзапросами.
Для оператора IN, применяемого к столбцам с индексами, оптимизатор в последних версиях стал эффективнее преобразовывать его в соединения или использовать стратегии поиска по индексу.
В планах выполнения появились дополнительные подсказки и статистические данные, которые помогают оптимизатору точнее оценивать селективность условий с IN.
Функциональных изменений в синтаксисе или базовой логике оператора IN в последних основных версиях не было.
Расширенные примеры применения оператора IN
Использование с выражением CASE:
SELECT ProductID, Name,
CASE WHEN ProductID IN (1, 2, 3)
THEN 'Special'
ELSE 'Regular'
END AS Status
FROM Production.Product;ProductID Name Status ----------- ------------- --------- 1 Product A Special 4 Product B Regular
IN с коррелированным подзапросом и агрегацией:
SELECT CustomerID, TotalDue
FROM Sales.SalesOrderHeader soh
WHERE TerritoryID IN (
SELECT TerritoryID
FROM Sales.SalesTerritory
WHERE SalesLastYear > 2000000
);Динамический SQL с IN (с использованием строки):
DECLARE @ids nvarchar(100) = '1,2,3';
DECLARE @sql nvarchar(max);
SET @sql = 'SELECT * FROM Products WHERE ProductID IN (' + @ids + ')';
EXEC sp_executesql @sql;Объединение нескольких условий IN:
SELECT *
FROM Employees
WHERE DepartmentID IN (1, 2, 3)
AND JobTitleID IN (SELECT JobTitleID FROM JobTitles WHERE Category = 'Technical');IN с табличной переменной:
DECLARE @filter TABLE (id int);
INSERT INTO @filter VALUES (10), (20), (30);
SELECT * FROM Orders WHERE CustomerID IN (SELECT id FROM @filter);IN внутри агрегатной функции для проверки вхождения в набор констант:
SELECT
COUNT(CASE WHEN StatusID IN (1, 2) THEN 1 END) AS ActiveCount,
COUNT(CASE WHEN StatusID IN (3, 4) THEN 1 END) AS InactiveCount
FROM Users;ActiveCount InactiveCount ----------- ------------- 150 75