NULLIF: примеры (SQL)
NULLIF(expression1, expression2): Same as expression1Описание функции NULLIF в MS SQL Server
Функция NULLIF является встроенной функцией в MS SQL Server, предназначенной для обработки и преобразования данных. Она сравнивает два выражения и возвращает значение NULL, если они равны. В противном случае возвращает значение первого выражения. Эта функция часто используется для предотвращения ошибок деления на ноль, обработки нежелательных или эквивалентных значений и упрощения логики запросов.
Синтаксис: NULLIF(expression1, expression2)
- expression1 - любое допустимое скалярное выражение, значение которого проверяется на равенство со вторым выражением.
- expression2 - любое допустимое скалярное выражение, с которым сравнивается первое выражение. Если типы данных выражений различаются, SQL Server выполняет неявное преобразование. При невозможности преобразования возникает ошибка.
Возвращаемое значение: Если expression1 равно expression2, функция возвращает NULL. В противном случае возвращается значение expression1. Тип данных результата соответствует типу данных первого выражения. Если первое выражение может принимать значение NULL, результат также может быть NULL.
Простые примеры использования NULLIF
Пример 1: Предотвращение деления на ноль.
SELECT 100 / NULLIF(0, 0) AS Result;Result ------- NULL
Пример 2: Замена конкретного значения на NULL.
SELECT NULLIF('N/A', 'N/A') AS Status;Status ------ NULL
Пример 3: Сравнение разных типов данных с неявным преобразованием.
SELECT NULLIF(5, '5') AS Comparison;Comparison ---------- NULL
Пример 4: Возврат первого выражения при неравенстве.
SELECT NULLIF('Active', 'Inactive') AS State;State ----- Active
Похожие функции в MS SQL Server
ISNULL(check_expression, replacement_value) - возвращает replacement_value, если check_expression равно NULL, иначе возвращает check_expression. Эта функция заменяет NULL на заданное значение, тогда как NULLIF, наоборот, создает NULL при совпадении.
COALESCE(expression1, expression2, ..., expressionN) - возвращает первое выражение в списке, которое не равно NULL. Позволяет обрабатывать несколько возможных NULL-значений, в отличие от NULLIF, который работает только с двумя выражениями и целенаправленно создает NULL.
CASE - оператор условной логики общего назначения. Может эмулировать поведение NULLIF: CASE WHEN expression1 = expression2 THEN NULL ELSE expression1 END. Используется, когда требуется более сложная логика сравнения.
Аналоги функции в других СУБД и языках
MySQL: Функция NULLIF идентична по синтаксису и поведению.
SELECT NULLIF(10, 10); -- Возвращает NULLNULL
Oracle: Функция NULLIF работает аналогично.
SELECT NULLIF(5, 5) FROM dual; -- Возвращает NULLNULL
PostgreSQL: Поведение NULLIF полностью соответствует стандарту SQL.
SELECT NULLIF('text', 'text'); -- Возвращает NULLNULL
SQLite: Поддерживает функцию NULLIF.
SELECT NULLIF(1, 2); -- Возвращает 11
Sybase ASE: Поддерживает функцию NULLIF с аналогичной семантикой.
Язык программирования C# (тернарный оператор): Аналогом можно считать конструкцию: expression1 == expression2 ? null : expression1.
Во всех перечисленных СУБД функция ведет себя единообразно, что облегчает перенос запросов между системами.
Типичные ошибки при работе с NULLIF
Ошибка 1: Использование выражений с несовместимыми типами данных, для которых невозможно неявное преобразование.
SELECT NULLIF(CAST('2023-01-01' AS DATE), 'Not a Date');Сообщение об ошибке преобразования типа данных.
Ошибка 2: Непонимание, что NULLIF возвращает NULL только при точном равенстве, включая сравнение с NULL. Сравнение NULL = NULL возвращает UNKNOWN, а не TRUE, поэтому NULLIF(NULL, NULL) также возвращает NULL (особенность реализации).
SELECT NULLIF(NULL, NULL); -- Возвращает NULL, а не первый аргументNULL
Ошибка 3: Ожидание, что функция изменит данные в таблице. NULLIF только возвращает значение в выборке, не модифицируя таблицу.
Ошибка 4: Забывание о том, что деление на NULL возвращает NULL, а не вызывает ошибку. Это может привести к неожиданным NULL в результатах арифметических операций.
SELECT 10 / NULLIF(5, 5) AS Result; -- Возвращает NULL, а не ошибкуResult ------- NULL
История изменений функции NULLIF
Функция NULLIF присутствует в MS SQL Server с ранних версий и является частью стандарта SQL. Значительных изменений в ее поведении или синтаксисе в последних основных версиях (SQL Server 2016, 2017, 2019, 2022) не было. Функция остается стабильной и полностью соответствует стандарту ANSI SQL.
В современных версиях SQL Server функция может использоваться в сочетании с новыми функциями, такими как CONCAT_WS или внутри оконных функций, но ее базовая логика не менялась.
Расширенные примеры применения NULLIF
Пример 1: Обработка среднего значения с исключением нулевых делителей.
SELECT AVG(TotalSales / NULLIF(NumTransactions, 0)) AS AvgTransaction FROM SalesData;Пример 2: Использование в UPDATE для очистки данных.
UPDATE Products SET Description = NULLIF(Description, 'N/A');Пример 3: Комбинирование с COALESCE для задания значения по умолчанию.
SELECT COALESCE(NULLIF(PhoneNumber, ''), 'Not Provided') AS Contact FROM Customers;Пример 4: Применение в вычисляемых столбцах.
ALTER TABLE Orders ADD DiscountedPrice AS Price * NULLIF(1 - DiscountPercent, 0);Пример 5: Использование с строковыми функциями для обработки пустых строк.
SELECT NULLIF(TRIM(Comments), '') AS CleanComments FROM Feedback;Пример 6: Вложенные NULLIF для обработки нескольких нежелательных значений (эмулируется через CASE).
SELECT CASE WHEN Status IN ('N/A', 'Pending') THEN NULL ELSE Status END AS CleanStatus FROM Orders;Пример 7: Защита от деления на ноль в агрегатных функциях с группировкой.
SELECT DepartmentID, SUM(Sales) / NULLIF(COUNT(DISTINCT SalespersonID), 0) AS AvgPerPerson FROM Sales GROUP BY DepartmentID;