sql - not - ¿Usar ISNULL versus usar COALESCE para verificar una condición específica?
isnull() sql (9)
Creo que no, pero COALESCE está en el estándar SQL ''92 y es compatible con más bases de datos diferentes. Si opta por la portabilidad, no use ISNULL.
Sé que se pueden pasar múltiples parámetros a COALESCE
, pero cuando quieres verificar solo una expresión para ver si no existe, ¿utilizas un valor predeterminado o es una mejor práctica usar ISNULL
en ISNULL
lugar?
¿Hay alguna ganancia de rendimiento entre los dos?
Donde solo hay una condición nula, ISNULL
tendrá menos sobrecarga. La diferencia es probablemente insignificante, sin embargo.
En COALESCE uno puede usar expresiones múltiples, devolverá un valor que no es nulo y ocurre primero ... por ejemplo
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)
Y en ISNULL si la expresión es nula, devolverá el segundo parámetro proporcionado, y por supuesto puede verificar solo una expresión ...
Entonces, si quieres verificar la expresión múltiple y seleccionar primero no nulo entre ellos, entonces usa coalesce de lo contrario busca ISNULL
Esta explicación da claridad sobre coalesce vs isnull
La función COALESCE en SQL devuelve la primera expresión no nula entre sus argumentos. La sintaxis para COALESCE es la siguiente:
COALESCE ("expression 1", "expressions 2", ...)
Es lo mismo que la siguiente declaración 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";
En SQL Server, la función ISNULL () se usa para reemplazar el valor NULL con otro valor.
select CountryName = ISNULL("columnname", ''INDIA'') from Countries
Coalesce return first expresión no nula donde isnull () se utiliza para reemplazar el valor nulo con nuestro valor deseado.
COALESCE es parte de los estándares de ANSI y está disponible en casi todas las bases de datos.
Al decidir entre ISNULL v COALESCE, los parámetros deben ser eliminados:
- COALESCE determina el tipo de salida en función de la prioridad del tipo de datos donde, como con ISNULL, el tipo de datos no está influenciado por la prioridad del tipo de datos.
Considere seguir declaraciones sql
DECLARE @c5 VARCHAR(5); SELECT ''COALESCE'', COALESCE(@c5, ''longer name'') UNION ALL SELECT ''ISNULL'', ISNULL(@c5, ''longer name'');
Resultados:
COALESCE longer name
ISNULL longe
Esto sucede porque ISNULL toma el tipo de datos del primer argumento, mientras que COALESCE inspecciona todos los elementos y elige el que mejor se ajusta (en este caso, VARCHAR (11))
Para obtener una explicación más detallada sobre cómo decidir entre COALESCE vs ISNULL, consulte esto: https://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/
Una cosa importante que no veo explícitamente indicada es que el tipo de salida de ISNULL
es similar a la primera expresión, pero con COALESCE
devuelve el tipo de datos de valor de mayor precedencia.
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''
Vale la pena mencionar que el manejo del tipo entre los dos también puede marcar la diferencia (consulte este elemento de respuesta relacionado (2) ).
Digamos que una consulta intenta usar un atajo para escribir una comparación nula:
select * from SomeTable
where IsNull(SomeNullableBitField, -1) != IsNull(SomeOtherNullableBitField, -1);
que es diferente de
select * from SomeTable
where coalesce(SomeNullableBitField, -1) != coalesce(SomeOtherNullableBitField, -1);
Porque en el primer caso, el IsNull () obliga al tipo a ser un bit (por lo que -1 se convierte en verdadero) mientras que el segundo caso promoverá ambos a un 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;
Hay un comentario / enlace similar (@Martin Smith) sobre la pregunta en sí.
Este problema informado en Microsoft Connect revela algunas diferencias entre COALESCE
e ISNULL
:
una parte temprana de nuestro procesamiento reescribe
COALESCE( expression1, expression2 )
comoCASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END
. En este ejemplo]:
COALESCE ( ( SELECT Nullable FROM Demo WHERE SomeCol = 1 ), 1 )
generamos
SELECT CASE WHEN (SELECT Nullable FROM Demo WHERE SomeCol = 1) IS NOT NULL THEN (SELECT Nullable FROM Demo WHERE SomeCol = 1) ELSE 1 END
Las etapas posteriores del procesamiento de consultas no comprenden que las dos subconsultas fueron originalmente la misma expresión, por lo que ejecutan la subconsulta dos veces ...
Una solución, aunque odio sugerirlo, es cambiar
COALESCE
aISNULL
, ya que este último no duplica la subconsulta.
NULL
y COALESCE
no son siempre intercambiables. Merece conocer sus diferencias para saber cuándo es mejor usar una sobre la otra:
La tabla anterior es una comparación entre ISNULL
y COALESCE
de Exam Ref 70-761 Querying Data with Transact-SQL
libro Exam Ref 70-761 Querying Data with Transact-SQL
escrito por Itzik Ben-Gan.
- Número de parámetros admitidos:
2
paraISNULL
vs>2
cuando se usaCOALESCE
-
ISNULL
es propiedad de la característica T-SQL yCOALESCE
es estándar ISO / ANSI SQL El tipo de datos del resultado es importante. Después de leer las notas en la tabla anterior, verifique los siguientes casos:
DECLARE @x VARCHAR(3) = NULL ,@y VARCHAR(10) = ''1234567890''; SELECT ISNULL(@x, @y) AS [ISNULL], COALESCE(@x, @y) AS [COALESCE];
El
ISNULL
obtiene el tipo de datos del primer argumento, ya que es el literal noNULL
. EsVARCHAR(3)
y es un resultado, los datos del segundo argumento se cortan para que coincidan. ConCOALESCE
el tipo de datos si se usa la precedencia más alta.DECLARE @x VARCHAR(8) = ''123x5'' ,@y INT = 123; SELECT ISNULL(@x, @y) AS [ISNULL]; SELECT COALESCE(@x, @y) AS [COALESCE];
El
ISNULL
está devolviendo el tipo de datos del primer argumento, mientras que enCOALESCE
estamos obteniendo el error, ya que elINT
tiene la precedencia más alta y la conversión del primer valor de argumento aINT
falla.La nulabilidad del resultado también puede ser importante. Por ejemplo:
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];
Vamos a verificar la propiedad
Nullable
de cada columna:Usando
COALESCE
tenemos una propiedadNOT NULL
de la columna establecida enYes
, solo cuando todas las entradas no son nula.De acuerdo con el estándar SQL, la expresión
COALESCE
se traduce a:CASE WHEN (<subquery>) IS NOT NULL THEN (<subquery>) ELSE 0 END
Si el resultado de la ejecución de la subconsulta en la cláusula WHEN no es NULL, SQL Server lo ejecutará por segunda vez en la cláusula THEN. En otras palabras, en tal caso, lo ejecuta dos veces. Solo si el resultado de la ejecución en la cláusula WHEN es NULL, SQL Server no ejecuta la subconsulta de nuevo, sino que devuelve la expresión ELSE. Por lo tanto, cuando se utilizan subconsultas, la función ISNULL tiene una ventaja de rendimiento.