IIF функция в T-SQL - использование в запросах SQL Server
Функция IIF в T-SQL позволяет быстро и просто добавлять логику if-else в SQL запросы. Давайте разберемся как она работает и где можно ее применить на практических примерах.
Описание и синтаксис IIF функции
Функция IIF появилась в SQL Server 2012 как сокращенный способ написания выражений CASE для добавления условной логики в запросы. Она принимает три параметра:
- Логическое выражение
- Значение, если выражение истинно
- Значение, если выражение ложно
Синтаксис выглядит следующим образом:
IIF(boolean_expression, true_value, false_value)Например, чтобы вернуть 'Высокий' или 'Низкий' в зависимости от значения переменной @score, можно написать:
DECLARE @score INT = 90; SELECT IIF(@score > 80, 'Высокий', 'Низкий');Это полностью эквивалентно более длинной конструкции CASE:
DECLARE @score INT = 90; SELECT CASE WHEN @score > 80 THEN 'Высокий' ELSE 'Низкий' END;К IIF применяются те же правила обработки NULL значений и определения возвращаемого типа данных, что и для CASE. Также IIF можно вкладывать друг в друга не более 10 раз, аналогично ограничению на вложенность CASE.
Примеры использования IIF в запросах
Рассмотрим несколько практических случаев применения функции IIF в T-SQL запросах.
Добавим условие в оператор SELECT для выборки разных столбцов в зависимости от значения переменной:
DECLARE @parameter VARCHAR(10) = 'type'; SELECT IIF(@parameter = 'name', first_name, IIF(@parameter = 'type', customer_type, 'Not found')) FROM customers;Изменим данные с помощью IIF при обновлении таблицы:
UPDATE products SET status = IIF(units_in_stock = 0, 'Out of stock', status);Заменим NULL значения в столбце на другие значения:
SELECT order_id, IIF(ship_date IS NULL, 'Not shipped', CONVERT(VARCHAR(10), ship_date)) AS ship_date FROM orders;Это лишь несколько начальных примеров использования IIF, которые показывают гибкость и универсальность этой функции для решения разных задач.
Далее мы рассмотрим более сложные случаи применения IIF с другими функциями, для обработки ошибок, оптимизации запросов и решения бизнес-задач.
Одна из сильных сторон IIF - это возможность комбинировать ее с другими функциями T-SQL. Рассмотрим несколько примеров.
С SUM
Чтобы посчитать сумму значений столбца только для определенных строк, можно использовать конструкцию:
SELECT SUM(IIF(year = 2022, revenue, 0)) AS revenue_2022, SUM(IIF(year = 2023, revenue, 0)) AS revenue_2023 FROM sales;С COALESCE
Функция COALESCE возвращает первое не NULL значение из списка. Совместно с IIF это позволяет гибко работать с отсутствующими данными:
SELECT order_id, COALESCE(shipped_date, IIF(shipped_date IS NULL, estimated_ship_date, NULL)) AS ship_date FROM orders;С CAST и CONVERT
Можно комбинировать IIF с функциями преобразования типов данных CAST и CONVERT, чтобы возвращать значения в нужном формате:
SELECT order_id, IIF(CAST(shipped_date AS DATE) = CAST(GETDATE() AS DATE), 'Shipped today', CONVERT(VARCHAR(10), shipped_date, 101) ) AS shipment FROM orders;Обработка ошибок с помощью IIF
Распространенное применение IIF - это обработка различных ошибок и исключений в запросах SQL. Рассмотрим конкретные случаи.
Деление на ноль
Чтобы избежать ошибки деления на ноль в вычислениях, можно написать:
SELECT order_id, IIF(units IS NULL OR units = 0, NULL, total / units) AS average FROM orders;Превышение ограничения по длине
Если результат выражения превышает максимально допустимый размер для данного типа, IIF позволяет вернуть усеченное значение:
SELECT name, IIF(LEN(description) > 50, LEFT(description, 50) + '...', description) AS description FROM products;Применение IIF для оптимизации запросов
IIF может использоваться не только для добавления логики в запросы SQL, но и для оптимизации их производительности. Конкретные приемы будут рассмотрены далее.
Фильтрация данных на стороне сервера
Вместо фильтрации строк на клиентской стороне, вы можете использовать IIF в предложении WHERE для фильтрации непосредственно на сервере:
SELECT * FROM products WHERE IIF(category_id = 1, price > 100, price > 50);Это позволит отправить на клиента уже отфильтрованный набор данных, сократив объем передаваемой информации.
Исключение дорогостоящих операций
Допустим, в запросе присутствует вызов внешней процедуры, который требует много времени. С помощью IIF можно исключить его выполнение для части данных:
SELECT *, IIF(created_date > '20210101', expensive_udf(id), NULL) AS extended_data FROM orders WHERE user_id = @id;Переключение соединений
В некоторых случаях имеет смысл использовать разные типы соединений в одном запросе, например внешнее и внутреннее. Это можно реализовать через CASE или IIF:
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id AND IIF(table1.date < '20220101', 1, 0) = 1Сравнение IIF и CASE
Несмотря на идентичность работы IIF и CASE, между ними есть некоторые отличия:
- Синтаксис IIF проще и компактнее
- CASE читается и воспринимается нагляднее
- IIF быстрее компилируется, CASE может выполняться эффективнее