Функции COALESCE и ISNULL в T-SQL – особенности и основные отличия

В языке T-SQL есть такие функции как COALESCE и ISNULL, которые умеют проверять входящие параметры на значение NULL, сегодня мы рассмотрим особенности этих функций, а также сравним их и определим, какие между ними есть отличия.

Функции COALESCE и ISNULL в T-SQL

Напомню, ранее мы с Вами уже рассмотрели основы программирования на языке T-SQL, а также составили краткий справочник по данному языку, но подробно о функциях COALESCE и ISNULL мы не разговаривали и уж тем более не сравнивали их. И так как у начинающих программистов T-SQL может сложиться впечатление, что функции COALESCE и ISNULL работают абсолютно одинаково, что на самом деле не так, я предлагаю поговорить об этих функциях более подробно, а именно узнать, какие между ними есть отличия и в каких случаях лучше использовать ту или иную функцию.

COALESCE

COALESCE – функция T-SQL, которая возвращает первое выражение из списка параметров, неравное NULL.

Синтаксис

COALESCE (входящие параметры [,…n])

Особенности

В качестве параметров могут выступать выражения любого типа данных, в том числе и подзапросы. В случае если все параметры равны NULL, то функция вернет значение NULL. Тип возвращаемого значения равняется типу данных значения с наибольшим приоритетом. Например, указано 5 параметров и все они имеют разные типы данных, то вернется первое значение отличное от NULL с типом данных, который имеет наибольший приоритет среди всех типов значений указанных в параметрах.

Как заявляют разработчики, т.е. это указанно в официальной документации, функция COALESCE является эквивалентов выражения CASE, т.е. всего лишь его синтаксическое сокращение. Для примера давайте напишем запрос, в котором будет два выражения, первое с использованием функции COALESCE, а второе с использованием CASE, которые будут работать одинаково.

 
   DECLARE @Var1 VARCHAR(5)
   DECLARE @Var2 VARCHAR(5)
   DECLARE @Var3 VARCHAR(5)
   
   SET @Var1 = NULL
   SET @Var2 = NULL
   SET @Var3 = 'Var3'
   
   SELECT COALESCE(@Var1, @Var2, @Var3, 'Все параметры пустые') AS [COALESCE], 
      CASE WHEN @Var1 IS NOT NULL THEN @Var1
           WHEN @Var2 IS NOT NULL THEN @Var2
           WHEN @Var3 IS NOT NULL THEN @Var3 
      ELSE 'Все параметры пустые'
      END AS [CASE]

Скриншот 1

ISNULL

ISNULL – функция T-SQL, которая заменяет значение NULL первого параметра, на указанное значение во втором параметре. Другими словами, в случае если первый параметр равен NULL, то возвращается второй параметр.

Синтаксис

ISNULL (check_expression, replacement_value)

Где

  • check_expression — выражение, проверяемое на NULL;
  • replacement_value — выражение, возвращаемое в случае, если значение check_expression равно NULL.

Особенности

Тип данных выражения replacement_value должен явно преобразовываться к типу данных значения check_expresssion, в итоге у функции ISNULL тип возвращаемого значения равен типу данных check_expression.

Пример

 
   DECLARE @Var1 VARCHAR(35)
   
   SET @Var1 = NULL
   
   SELECT ISNULL (@Var1, 'Значение первого параметра NULL') AS [ISNULL]

Скриншот 2

Сравнение COALESCE и ISNULL — их основные отличия

На первый взгляд может показаться, что эти функции работают одинаково за исключением того, что у функции ISNULL два параметра, а у COALESCE их может быть несколько, но помимо этого, есть и другие более важные отличия.

  • Первое — это, как было уже отмечено, функция ISNULL принимает только 2 параметра, а COALESCE принимает изменяемое число параметров;
  • Функция ISNULL возвращает тип данных первого параметра, поэтому замещающее значение, указанное во втором параметре, должно явно преобразовываться. В то время, как функция COALESCE возвращает тип данных значения с наибольшим приоритетом (также как и в CASE);
  • Следует помнить, что выражение, возвращаемое функцией ISNULL, рассматривается SQL сервером как не NULL, а COALESCE наоборот допускающее NULL. Это можно будет заметить, например, при создании ограничения первичного ключа на вычисляемом столбце, т.е. с использованием ISNULL(NULL, 1) — это можно будет сделать, а с COALESCE(NULL, 1) нет (в примерах ниже мы это рассмотрим).

Примеры, показывающие отличия в работе функций COALESCE и ISNULL

Пример 1. Отличие в количестве параметров.

 
   DECLARE @Var1 INT
   DECLARE @Var2 INT
   DECLARE @Var3 INT
   
   SET @Var1 = NULL
   SET @Var2 = NULL
   SET @Var3 = 1
   
   SELECT COALESCE(@Var1, @Var2, @Var3) AS [COALESCE],
          ISNULL(@Var1, @Var2) AS [ISNULL]--Будет ошибка, если указать третий параметр

Скриншот 3

Пример 2. Отличие в возвращаемом типе данных (текстовые данные).

 
   DECLARE @Var1 VARCHAR(5)
   DECLARE @Var2 VARCHAR(20)
   
   SET @Var1 = NULL
   SET @Var2 = 'Первый параметр NULL'

   /*
      Функция COALESCE вернет значение с типом varchar(20),
      т.е. как у переменной @Var2, а ISNULL с типом varchar(5)
      как у первого параметра @Var1, т.е. произойдет усечение данных
   */
   SELECT COALESCE(@Var1, @Var2) AS [COALESCE],
          ISNULL(@Var1, @Var2) AS [ISNULL]

Скриншот 4

Пример 3. Отличие в возвращаемом типе данных (целочисленный тип данных).

 
   DECLARE @Var1 TINYINT
   DECLARE @Var2 INT
   
   SET @Var1 = NULL
   SET @Var2 = -1
   
   /*
        Функция COALESCE вернет более приоритетный тип данных, т.е. INT,
        а функция ISNULL выдаст ошибку, 
        так как нельзя явно преобразовать значение -1 в тип TINYINT,
        который является типом первого параметра 
   */
   PRINT COALESCE(@Var1, @Var2)
   PRINT ISNULL(@Var1, @Var2)

Скриншот 5

Пример 4. Отличие в возвращаемом значении, т.е. результат ISNULL — это не NULL, а результат COALESCE — это NULL.

В примере ниже мы создаем временные таблицы с ограничением первичного ключа на вычисляемом столбце, в первой таблице вычисляемый столбец использует в своем выражении ISNULL(column1, 1), где column1 допускает значения NULL, а во второй таблице COALESCE(column1, 1), т.е. по сути, эквивалентные выражения. В итоге первая таблица будет создана успешно, так как SQL сервер подразумевает, что возвращаемое значение функции ISNULL не допускает значений NULL. А при создании второй таблицы выйдет ошибка, так как в этом случае SQL сервер предполагает, что возвращаемое значение функции COALESCE будет NULL.

 
   --Таблица будет создана успешно
   CREATE TABLE #TempTable1 
        ( 
                column1 integer NULL, 
                column2 AS ISNULL(column1, 1) PRIMARY KEY 
        )
   IF OBJECT_ID('tempdb..#TempTable1') IS NOT NULL
        BEGIN
                PRINT 'Таблица #TempTable1 успешно создана'
                DROP TABLE #TempTable1 --Сразу удаляем ее
        END
   GO

   --Ошибка
   CREATE TABLE #TempTable2 
        (       
           column1 integer NULL, 
           column2 AS COALESCE(column1, 1) PRIMARY KEY
        )

Скриншот 6

Рекомендации по использованию функций COALESCE и ISNULL

Функцию COALESCE рекомендовано использовать тогда, когда просто необходимо вернуть первое значение отличное от NULL.

Функцию ISNULL используйте для замены значения NULL, при возможном его появлении в выражении, на другое значение, при этом в качестве второго параметра лучше указывать выражение 100% отличное от NULL, например константу. Другими словами, чтобы предотвратить ситуацию, когда мы хотим использовать значение, а оно у нас NULL.

Если говорить о производительности, то в случаях, когда в качестве выражения выступает подзапрос, а по документации SQL сервера в функции COALESCE значение подзапроса будет вычисляться дважды, можно сделать вывод, что в этих случаях быстрей будет ISNULL, так как в ней значение вычисляется один раз. В остальных случаях разница будет незначительна.

Заметка! Начинающим программистам рекомендую почитать мою книгу «Путь программиста T-SQL. Самоучитель по языку Transact-SQL», в ней я подробно, с большим количеством примеров, рассказываю про другие полезные функции и возможности языка Transact-SQL.

На этом у меня все, удачи!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.