Compartir vía

COALESCE (Transact-SQL)

  • Artículo

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Punto de conexión de análisis SQL en Microsoft FabricWarehouse en Microsoft FabricBase de datos SQL de Microsoft Fabric

Evalúa los argumentos en orden y devuelve el valor actual de la primera expresión que inicialmente no se evalúa como NULL. En el ejemplo siguiente se devuelve el tercer valor porque el tercer valor es el primer valor que no es NULL.

SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value');

Nota

Si desea concatenar cadenas, use STRING_AGG en su lugar.

Convenciones de sintaxis de Transact-SQL

Sintaxis

COALESCE ( expression [ , ...n ] )

Argumentos

expression

Expresión de cualquier tipo.

Tipos devueltos

Devuelve el tipo de datos de expresión con la prioridad de tipo de datos más alta. Si todas las expresiones no aceptan valores NULL, el resultado se escribe como que no acepta valores NULL.

Observaciones

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

Comparar COALESCE y CASE

La expresión COALESCE es un método abreviado sintáctico de la expresión CASE. Es decir, el optimizador de consultas vuelve a escribir el código COALESCE(<expression1>, ...n) como la siguiente expresión de 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 (expresión1, expresión2, expresiónN, etc.) se evalúan varias veces. Una expresión de valor que contiene una subconsulta se considera no determinista y la subconsulta se evalúa dos veces. El resultado está conforme con el estándar SQL. 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 NULL en el nivel de aislamiento READ COMMITTED. Para asegurarse de que se devuelven resultados estables, use el nivel de aislamiento SNAPSHOT ISOLATION, o bien 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;

Comparar COALESCE e ISNULL

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

  1. Dado que ISNULL es una función, solo se evalúa 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 usa el tipo de datos del primer parámetro y COALESCE sigue las reglas de expresión de CASE para devolver el tipo de datos de valor con la prioridad más alta.

  3. La nulabilidad de la expresión de resultado es diferente para ISNULL y COALESCE. El valor devuelto ISNULL siempre se considera NOT NULLcapaz (suponiendo que el valor devuelto sea un valor que no acepta valores NULL). Por el contrario, COALESCE con parámetros que no son NULL se considera NULL. Por tanto, aunque las expresiones ISNULL(NULL, 1) y COALESCE(NULL, 1) son equivalentes, tienen valores de nulabilidad diferentes. Estos valores marcan una diferencia si usa estas expresiones en columnas calculadas, creando restricciones de clave o haciendo que el valor devuelto de una función escalar definida por el usuario (UDF) sea determinista, de modo que se pueda indexar 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 INT 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 INT NULL,
    col2 AS COALESCE (col1, 0),
    col3 AS ISNULL(col1, 0) PRIMARY KEY
);

  4. Las validaciones de ISNULL y COALESCE también son diferentes. Por ejemplo, con NULL, un valor ISNULL se convierte en int, mientras que con COALESCE es necesario proporcionar un tipo de datos.

  5. ISNULL toma solo dos parámetros. Por el contrario COALESCE toma un número variable de parámetros.

Ejemplos

Los ejemplos de código de este artículo usan la base de datos de ejemplo de AdventureWorks2022 o AdventureWorksDW2022, que puede descargar de la página principal de ejemplos de Microsoft SQL Server y proyectos de comunidad.

A. Devolver datos de la primera columna que tiene un valor distinto de NULL

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

Name         Color      ProductNumber
------------ ---------- -------------
Socks, Mens  NULL       PN1278
Socks, Mens  Blue       PN1965
NULL         White      PN9876

A continuación, ejecutamos la siguiente consulta COALESCE:

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

Este es el conjunto de resultados.

Name         Color      ProductNumber  FirstNotNull
------------ ---------- -------------  ------------
Socks, Mens  NULL       PN1278         PN1278
Socks, Mens  Blue       PN1965         Blue
NULL         White      PN9876         White

En la primera fila, el valor de FirstNotNull es PN1278, no Socks, Mens. Esto se debe a que en el ejemplo la columna Name no se especificó como un parámetro para COALESCE.

B. Devolver el valor distinto de NULL en una tabla de salarios

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, use COALESCE para recibir solo el valor no NULL que se encuentra en hourly_wage, salaryy 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

Este es el conjunto de resultados.

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

Contenido relacionado