IIF: примеры (SQL)

Простые примеры использования

Пример 1: Базовое использование для возврата текстового значения.

SELECT IIF(1 > 0, 'Истина', 'Ложь') AS Result;

Result
-------
Истина

Пример 2: Использование с числовыми значениями и разными типами данных.

SELECT IIF(10 = 5, 100, 200.0) AS Result;

Result
------
200.0

Пример 3: Работа с NULL. Если логическое выражение оценивается как NULL, результат будет false_value.

SELECT IIF(NULL, 'A', 'B') AS Result;

Result
-------
B

Пример 4: Вложенные вызовы IIF для имитации структуры IF...ELSE IF.

DECLARE @Value INT = 15;
SELECT IIF(@Value > 100, 'Большой',
           IIF(@Value > 10, 'Средний', 'Маленький')) AS Category;

Category
---------
Средний

Альтернативные функции в MS SQL

CASE — это основной и наиболее гибкий условный оператор в SQL. Он является стандартом ANSI и работает во всех версиях SQL Server. Функция IIF по сути является его синтаксическим сахаром.

Пример:

SELECT CASE WHEN 1 > 0 THEN 'Истина' ELSE 'Ложь' END AS Result;

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

Пример:

SELECT CHOOSE(2, 'Понедельник', 'Вторник', 'Среда') AS DayOfWeek;

Когда что использовать: IIF предпочтительнее для простых условий с двумя исходами, где требуется лаконичный синтаксис. CASE необходим для сложных условий с несколькими ветвлениями WHEN или в версиях SQL Server старше 2012. CHOOSE применяется для выборки по индексу.

Аналоги в других системах управления базами данных

MySQL: Использует функцию IF(). Синтаксис и логика аналогичны MS SQL IIF.

SELECT IF(1 > 0, 'Истина', 'Ложь');

Oracle: Не имеет встроенной функции IIF. Вместо нее используется стандартный оператор CASE или функция DECODE (нестандартная, только для равенства).

-- Использование CASE
SELECT CASE WHEN 1 > 0 THEN 'Истина' ELSE 'Ложь' END FROM dual;
-- Использование DECODE
SELECT DECODE(SIGN(1-0), 1, 'Истина', 'Ложь') FROM dual;

PostgreSQL: Поддерживает стандартный CASE. Также имеет функцию COALESCE и NULLIF для частных случаев обработки NULL.

SELECT CASE WHEN 1 > 0 THEN 'Истина' ELSE 'Ложь' END;

SQLite: Поддерживает функцию IIF(), которая ведет себя аналогично MS SQL.

SELECT IIF(1 > 0, 'Истина', 'Ложь');

Распространенные ошибки

Ошибка 1: Попытка использовать нелогическое выражение в качестве первого аргумента.

-- Вызовет ошибку "Недопустимый тип данных"
SELECT IIF('Текст', 1, 2);

Сообщение об ошибке: An expression of non-boolean type specified in a context where a condition is expected.

Ошибка 2: Несовместимые типы данных в ветках true_value и false_value, которые не могут быть неявно приведены.

-- Может привести к ошибке в зависимости от контекста
SELECT IIF(1=1, CAST('2023-01-01' AS DATETIME), 'Не дата');

Ошибка 3: Ожидание, что функция оценивает только нужную ветку. В MS SQL Server всегда оцениваются оба выражения, true_value и false_value, что может привести к ошибке деления на ноль или вызову функции с побочными эффектами.

-- Вызовет ошибку "Деление на ноль", хотя условие ложно
SELECT IIF(1=0, 100/1, 100/0) AS Result;

История изменений

Функция IIF была введена в SQL Server 2012 (версия 11.x) как часть усовершенствований языка Transact-SQL, направленных на большую совместимость с Microsoft Access и упрощение синтаксиса.

С момента своего появления синтаксис и поведение функции не претерпевали значительных изменений в последующих версиях SQL Server (2014, 2016, 2017, 2019, 2022).

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

Расширенные примеры применения

Пример 1: Использование IIF в предложении ORDER BY для управления сортировкой.

DECLARE @SortAsc BIT = 0;
SELECT ProductID, Name
FROM Production.Product
ORDER BY
    IIF(@SortAsc = 1, Name, NULL) ASC,
    IIF(@SortAsc = 0, Name, NULL) DESC;

Пример 2: Агрегация с условием внутри. Подсчет количества строк, удовлетворяющих условию.

SELECT
    COUNT(*) AS Total,
    SUM(IIF(Color = 'Red', 1, 0)) AS RedCount,
    AVG(IIF(Color = 'Red', ListPrice, NULL)) AS AvgRedPrice
FROM Production.Product;

Пример 3: Группировка значений в пользовательские категории непосредственно в SELECT.

SELECT
    ProductID,
    Name,
    ListPrice,
    IIF(ListPrice > 1000, 'Премиум',
        IIF(ListPrice > 100, 'Стандарт', 'Бюджет')) AS PriceCategory
FROM Production.Product;

Пример 4: Проверка на NULL и установка значения по умолчанию в одном выражении.

SELECT
    ProductID,
    Name,
    IIF(Color IS NULL, 'Не указан', Color) AS SafeColor
FROM Production.Product;

Пример 5: Использование скалярного подзапроса в качестве аргумента. Важно помнить, что подзапрос выполнится, даже если его результат не будет выбран.

SELECT
    ProductID,
    Name,
    IIF(EXISTS(SELECT 1 FROM Sales.SalesOrderDetail sod WHERE sod.ProductID = p.ProductID),
        'Продавался',
        'Нет продаж') AS SalesStatus
FROM Production.Product p;