COALESCE (Transact-SQL)

 

ESTE TEMA SE APLICA A: síSQL Server (a partir de 2008)síBase de datos SQL de AzuresíAlmacenamiento de datos SQL de Azure síAlmacenamiento de datos paralelos

Evalúa los argumentos en orden y devuelve el valor actual de la primera expresión que inicialmente no se evalúa como NULL.

Topic link icon Convenciones de sintaxis de Transact-SQL

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  
  
COALESCE ( expression [ ,...n ] )   

expresión
Es un expresión de cualquier tipo.

Devuelve el tipo de datos de expresión con la mayor prioridad de tipo de datos. Si ninguna de las expresiones admiten valores NULL, el resultado tiene un tipo que no admite valores NULL.

Si todos los argumentos son NULL, COALESCE devuelve NULL. Al menos uno de los valores NULL debe ser NULL con tipo.

La expresión COALESCE es un método abreviado sintáctico de la expresión CASE. Es decir, el código COALESCE(expression1,.. .n) se ha reescrito por el optimizador de consultas como la siguiente expresión CASE:

CASE

WHEN (expression1 IS NOT NULL) THEN expression1

WHEN (expression2 IS NOT NULL) THEN expression2

...

ELSE expressionN

END

Esto significa que los valores de entrada (expression1, expression2, expresiónn, etc.) se evaluarán varias veces. Además, de acuerdo con el estándar SQL, si una expresión de valor contiene una subconsulta se considera no determinista, y la subconsulta se evalúa dos veces. En cualquier caso, se pueden devolver resultados diferentes entre la primera evaluación y las evaluaciones posteriores.

Por ejemplo, cuando se ejecuta el código COALESCE((subquery), 1), la subconsulta se evalúa dos veces. En consecuencia, podrá obtener resultados diferentes en función del nivel de aislamiento de la consulta. Por ejemplo, en un entorno multiusuario, el código puede devolver el valor NULL en el nivel de aislamiento READ COMMITTED. Para asegurarse de que se devuelven resultados estables, utilice el nivel de aislamiento SNAPSHOT ISOLATION, o reemplace COALESCE por la función ISNULL. Como alternativa, puede volver a escribir la consulta para insertar la subconsulta en una subselección como se muestra en el ejemplo siguiente.

SELECT CASE WHEN x IS NOT NULL THEN x ELSE 1 END  
from  
(  
SELECT (SELECT Nullable FROM Demo WHERE SomeCol = 1) AS x  
) AS T;  
  

La función ISNULL y la expresión COALESCE tienen una finalidad similar, pero pueden comportarse de forma diferente.

  1. Dado que ISNULL es una función, se evalúa solo una vez. Como se ha descrito anteriormente, los valores de entrada de la expresión COALESCE se pueden evaluar varias veces.

  2. La determinación del tipo de datos de la expresión resultante es diferente. ISNULL utiliza el tipo de datos del primer parámetro, mientras que COALESCE sigue las reglas de la expresión CASE y devuelve el tipo de datos del valor cuya prioridad es mayor.

  3. La nulabilidad de la expresión de resultado es diferente para ISNULL y COALESCE. El valor devuelto por ISNULL siempre se considera NOT NULL (suponiendo que el valor devuelto no admita NULL), mientras que COALESCE con parámetros que no son NULL se considera NULL. Así, aunque las expresiones ISNULL(NULL, 1) y COALESCE(NULL, 1) son equivalentes, tienen valores de nulabilidad diferentes. Esto varía si utiliza estas expresiones en columnas calculadas, crea restricciones de clave, o hace que el valor devuelto de un UDF escalar sea determinista para poder indizarlo, como se muestra en el ejemplo siguiente.

    USE tempdb;  
    GO  
    -- This statement fails because the PRIMARY KEY cannot accept NULL values  
    -- and the nullability of the COALESCE expression for col2   
    -- evaluates to NULL.  
    CREATE TABLE #Demo   
    (   
    col1 integer NULL,   
    col2 AS COALESCE(col1, 0) PRIMARY KEY,   
    col3 AS ISNULL(col1, 0)   
    );   
    
    -- This statement succeeds because the nullability of the   
    -- ISNULL function evaluates AS NOT NULL.  
    
    CREATE TABLE #Demo   
    (   
    col1 integer NULL,   
    col2 AS COALESCE(col1, 0),   
    col3 AS ISNULL(col1, 0) PRIMARY KEY   
    );  
    
    
  4. Las validaciones para ISNULL y COALESCE también son diferentes. Por ejemplo, un valor NULL para ISNULL se convierte en int mientras que con COALESCE, debe proporcionar un tipo de datos.

  5. ISNULL acepta solo 2 parámetros, mientras que COALESCE acepta un número variable de parámetros.

A. Ejecutar un ejemplo sencillo

En el ejemplo siguiente se muestra cómo COALESCE selecciona los datos de la primera columna que tiene un valor no nulo. En este ejemplo se usa la base de datos AdventureWorks2012.

SELECT Name, Class, Color, ProductNumber,  
COALESCE(Class, Color, ProductNumber) AS FirstNotNull  
FROM Production.Product;  
  

B. Ejecutar un ejemplo complejo

En este ejemplo, la tabla wages incluye tres columnas con información acerca del sueldo anual de los empleados: la tarifa por hora, el salario y la comisión. No obstante, un empleado recibe solo un tipo de sueldo. Para determinar el importe total pagado a todos los empleados, utilice COALESCE para obtener solo los valores no NULL que se encuentran en hourly_wage, salary y commission.

SET NOCOUNT ON;  
GO  
USE tempdb;  
IF OBJECT_ID('dbo.wages') IS NOT NULL  
    DROP TABLE wages;  
GO  
CREATE TABLE dbo.wages  
(  
    emp_id        tinyint   identity,  
    hourly_wage   decimal   NULL,  
    salary        decimal   NULL,  
    commission    decimal   NULL,  
    num_sales     tinyint   NULL  
);  
GO  
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)  
VALUES  
    (10.00, NULL, NULL, NULL),  
    (20.00, NULL, NULL, NULL),  
    (30.00, NULL, NULL, NULL),  
    (40.00, NULL, NULL, NULL),  
    (NULL, 10000.00, NULL, NULL),  
    (NULL, 20000.00, NULL, NULL),  
    (NULL, 30000.00, NULL, NULL),  
    (NULL, 40000.00, NULL, NULL),  
    (NULL, NULL, 15000, 3),  
    (NULL, NULL, 25000, 2),  
    (NULL, NULL, 20000, 6),  
    (NULL, NULL, 14000, 4);  
GO  
SET NOCOUNT OFF;  
GO  
SELECT CAST(COALESCE(hourly_wage * 40 * 52,   
   salary,   
   commission * num_sales) AS money) AS 'Total Salary'   
FROM dbo.wages  
ORDER BY 'Total Salary';  
GO  

El conjunto de resultados es el siguiente.

Total Salary

------------

10000.00

20000.00

20800.00

30000.00

40000.00

41600.00

45000.00

50000.00

56000.00

62400.00

83200.00

120000.00

(12 row(s) affected)

C: ejemplo sencillo de

En el ejemplo siguiente se muestra cómo COALESCE selecciona los datos de la primera columna que tiene un valor no nulo. Para este ejemplo, supongamos que la Products tabla contiene estos datos:

Name Color ProductNumber

------------ ---------- -------------

Socks, Mens NULL PN1278

Socks, Mens Blue PN1965

NULL White PN9876

A continuación, ejecutar la siguiente consulta de COMBINACIÓN:

SELECT Name, Color, ProductNumber, COALESCE(Color, ProductNumber) AS FirstNotNull   
FROM Products ;  

El conjunto de resultados es el siguiente.

Name Color ProductNumber FirstNotNull

------------ ---------- ------------- ------------

Socks, Mens NULL PN1278 PN1278

Socks, Mens Blue PN1965 Blue

NULL White PN9876 White

Observe que en la primera fila, el FirstNotNull valor es "PN1278", no "Socks, hombres". Esto es porque la Name columna no se especificó como un parámetro para COALESCE en el ejemplo.

D: ejemplo complejo

En el ejemplo siguiente se usa COALESCE para comparar los valores de tres columnas y devolver solo el valor de no null que se encuentran en las columnas.

CREATE TABLE dbo.wages  
(  
    emp_id        tinyint   NULL,  
    hourly_wage   decimal   NULL,  
    salary        decimal   NULL,  
    commission    decimal   NULL,  
    num_sales     tinyint   NULL  
);  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (1, 10.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (2, 20.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (3, 30.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (4, 40.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (5, NULL, 10000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (6, NULL, 20000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (7, NULL, 30000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (8, NULL, 40000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (9, NULL, NULL, 15000, 3);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (10,NULL, NULL, 25000, 2);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (11, NULL, NULL, 20000, 6);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (12, NULL, NULL, 14000, 4);  
  
SELECT CAST(COALESCE(hourly_wage * 40 * 52,   
   salary,   
   commission * num_sales) AS decimal(10,2)) AS TotalSalary   
FROM dbo.wages  
ORDER BY TotalSalary;  

El conjunto de resultados es el siguiente.

Total Salary

------------

10000.00

20000.00

20800.00

30000.00

40000.00

41600.00

45000.00

50000.00

56000.00

62400.00

83200.00

120000.00

ISNULL ( Transact-SQL )
CASO ( Transact-SQL )

Adiciones de comunidad

AGREGAR
Mostrar: