Использование ISNULL против использования COALESCE для проверки определенного состояния?

Я знаю, что несколько параметров могут быть переданы в COALESCE, но если вы хотите проверьте только одно выражение, чтобы убедиться, что оно не существует, используете ли вы по умолчанию или лучше использовать ISNULL вместо этого?

Есть ли какой-либо выигрыш в производительности между двумя?

Ответ 1

Эта проблема, о которой сообщается в Microsoft Connect, показывает некоторые различия между COALESCE и ISNULL:

ранняя часть нашей обработки переписывает COALESCE( expression1, expression2 ) как CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END. В [этом примере]:
COALESCE ( ( SELECT Nullable
             FROM Demo
             WHERE SomeCol = 1 ), 1 )
порождаем:
SELECT CASE
          WHEN (SELECT Nullable FROM Demo WHERE SomeCol = 1) IS NOT NULL
          THEN (SELECT Nullable FROM Demo WHERE SomeCol = 1)
          ELSE 1
       END
Более поздние этапы обработки запросов не понимают, что оба подзапроса изначально были одним и тем же выражением, поэтому они дважды выполняют подзапрос...

Обходным решением, хотя я не хочу его предлагать, является изменение COALESCE на ISNULL, поскольку последнее не дублирует подзапрос.

Ответ 2

Я думаю, что нет, но COALESCE находится в стандарте SQL '92 и поддерживается более разными базами данных. Если вы идете на переносимость, не используйте ISNULL.

Ответ 3

В COALESCE вы можете иметь несколько выражений, где, как в ISNULL, вы можете проверить только одно выражение

COALESCE ( expression [ ,...n ] ) 

ISNULL ( check_expression , replacement_value )

Ответ 4

Следует отметить, что обработка типов между ними также может иметь значение (см. этот связанный элемент ответа (2)).

Скажите, что запрос пытается использовать ярлык для записи нулевого сравнения:

select * from SomeTable
 where IsNull(SomeNullableBitField, -1) != IsNull(SomeOtherNullableBitField, -1);

который отличается от

select * from SomeTable
 where coalesce(SomeNullableBitField, -1) != coalesce(SomeOtherNullableBitField, -1);

Поскольку в первом случае IsNull() заставляет тип быть битом (поэтому -1 преобразуется в true), тогда как второй случай будет способствовать как для int.

with input as 
(
  select convert(bit, 1) as BitOn,      
         convert(bit, 0) as BitOff,
         convert(bit, null) as BitNull
)
select BitOn, 
       BitOff,
       BitNull,
       IsNull(BitOn, -1) IsNullBitOn,         -- true
       IsNull(BitOff, -1) IsNullBitOff,       -- false
       IsNull(BitNull, -1) IsNullBitNull,     -- true, converts the -1 to bit
       coalesce(BitOn, -1) CoalesceBitOn,     -- 1
       coalesce(BitOff, -1) CoalesceBitOff,   -- 0       
       coalesce(BitNull, -1) CoalesceBitNull  -- -1
  from input;

В вопросе есть аналогичный комментарий/ссылка (@Martin Smith).

Ответ 5

Одна важная вещь, которую я не вижу явно, заключается в том, что тип вывода ISNULL аналогичен первому выражению, но с COALESCE он возвращает тип данных с наивысшим приоритетом.

DECLARE @X VARCHAR(3) = NULL
DECLARE @Y VARCHAR(10) = '123456789'
/* The datatype returned is similar to X, or the first expression*/
SELECT ISNULL(@X, @Y) ---> Output is '123'
/* The datatype returned is similar to Y, or to the value of highest precedence*/
SELECT COALESCE(@X, @Y) ---> Output is '123456789'

Ответ 6

Это объяснение дает ясно о coalesce vs isnull

Функция COALESCE в SQL возвращает первое выражение, отличное от NULL, среди его аргументов. Синтаксис COALESCE выглядит следующим образом:

 COALESCE ("expression 1", "expressions 2", ...)

Это то же самое, что и следующий оператор CASE:

SELECT CASE ("column_name")
  WHEN "expression 1 is not NULL" THEN "expression 1"
  WHEN "expression 2 is not NULL" THEN "expression 2"
  ...
  [ELSE "NULL"]
  END
FROM "table_name";

В SQL Server функция ISNULL() используется для замены значения NULL другим значением.

select CountryName = ISNULL("columnname", 'INDIA') from Countries

Coalesce возвращает первое ненулевое выражение, где as isnull() используется для замены нулевого значения нашим искомым значением.

COALESCE является частью стандартов ANSI и доступен практически во всех базах данных.

при выборе между ISNULL v COALESCE необходимо устранить параметры:

COALESCE определяет тип вывода, основанный на приоритете типа данных, где, как и в ISNULL, тип данных не зависит от приоритета типа данных.

Рассмотрим следующие операторы sql

DECLARE @c5 VARCHAR(5);
SELECT 'COALESCE', COALESCE(@c5, 'longer name')
UNION ALL
SELECT 'ISNULL',   ISNULL(@c5,   'longer name');

Результаты:

COALESCE longer name
ISNULL   longe

Это происходит потому, что ISNULL берет тип данных первого аргумента, а COALESCE проверяет все элементы и выбирает наилучшее соответствие (в этом случае VARCHAR (11))

Для получения более подробного объяснения при выборе между COALESCE и ISNULL проверьте следующее: https://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/

Ответ 7

Если существует только одно нулевое условие, ISNULL будет иметь меньше накладных расходов. Разница, вероятно, незначительна.

Ответ 8

NULL и COALESCE не всегда взаимозаменяемы. Он заслуживает знать свои различия, чтобы знать, когда лучше использовать одно над другим:

В приведенной выше таблице показано сравнение между ISNULL и COALESCE из Exam Ref 70-761 Querying Data with Transact-SQL книги, написанной Ициком Бен-Ганом.

Число поддерживаемых параметров - 2 для ISNULL vs >2 при использовании COALESCE
ISNULL - это собственная функция T-SQL, а COALESCE - стандарт ISO/ANSI SQL
Важен тип данных результата. Прочитав примечания в таблице выше, проверьте следующие случаи:
```
DECLARE @x VARCHAR(3)  = NULL
       ,@y VARCHAR(10) = '1234567890';

SELECT ISNULL(@x, @y) AS [ISNULL], COALESCE(@x, @y) AS [COALESCE];
```
ISNULL получает тип данных первого аргумента, поскольку он не является NULL литерал. Это VARCHAR(3) и является результатом, данные второго аргумента разрезается, чтобы соответствовать ему. С COALESCE тип данных, если высокий приоритет б.
```
DECLARE @x VARCHAR(8)  = '123x5'
       ,@y INT = 123;

SELECT ISNULL(@x, @y) AS [ISNULL];
SELECT COALESCE(@x, @y) AS [COALESCE];
```
ISNULL возвращает тип данных первого аргумента, а в COALESCE мы получаем ошибку, так как INT имеет самый высокий приоритет и преобразование значения первого аргумента в INT не выполняется.

Значение nullability результата также может быть важным. Например,

DECLARE @x VARCHAR(3) = NULL
       ,@y VARCHAR(3) = NULL;

DROP TABLE IF EXISTS [dbo].[DataSource01];

SELECT ISNULL(10, 20) AS [C1]
      ,ISNULL(@x, 'text') AS [C2]
      ,ISNULL(@x, @y) AS [C3]
INTO [dbo].[DataSource01];

DROP TABLE IF EXISTS [dbo].[DataSource02];

SELECT COALESCE(10, 20) AS [C1]
      ,COALESCE(@x, 'text') AS [C2]
      ,COALESCE(@x, @y) AS [C3]
INTO [dbo].[DataSource02];

Проверить свойство Nullable каждого столбца:

Используя COALESCE, мы имеем свойство NOT NULL столбца, установленного в Yes, только когда все входы не имеют нулевого значения.

В соответствии со стандартом SQL выражение COALESCE преобразуется в:
```
CASE WHEN (<subquery>) IS NOT NULL THEN (<subquery>) ELSE 0 END
```
Если результат выполнения подзапроса в предложении WHEN isnt NULL, SQL Server выполняет его второй раз в предложении THEN. Другими словами, в таком случае он выполняет его дважды. Только если результат выполнения в предложении WHEN равен NULL, SQL Server doesnt снова выполнить подзапрос, скорее вернет выражение ELSE. Так при использовании подзапросов функция ISNULL имеет производительность преимущество.

Ответ 9

В COALESCE можно использовать несколько выражений, оно вернет значение, которое не является нулевым и происходит первым... например

DECLARE @Value1 INT, @Value2 INT, @Value3 INT, @Value4 INT
SELECT @Value2 = 2, @Value4 = 4
SELECT COALESCE(@Value1, @Value2, @Value3, @Value4)
SELECT COALESCE(@Value1, @Value4, @Value3, @Value2)

И в ISNULL, если выражение null, оно вернет второй предоставленный параметр, и, конечно, вы можете проверить только одно выражение...

Итак, если вы хотите проверить несколько выражений и выбрать сначала не null среди них, тогда используйте coalesce, иначе перейдите в ISNULL