COALESCE (Transact-SQL)COALESCE (Transact-SQL)

SE APLICA A: síSQL Server síAzure SQL Database síAzure Synapse Analytics (SQL DW) síAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

Evalúa los argumentos en orden y devuelve el valor actual de la primera expresión que inicialmente no se evalúa como NULL.Evaluates the arguments in order and returns the current value of the first expression that initially doesn't evaluate to NULL. Por ejemplo, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); devuelve el tercer valor porque el tercer valor es el primer valor que no es NULL.For example, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); returns the third value because the third value is the first value that isn't null.

Icono de vínculo de tema Convenciones de sintaxis de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxisSyntax

COALESCE ( expression [ ,...n ] )   

ArgumentosArguments

expressionexpression
Es una expresión de cualquier tipo.Is an expression of any type.

Tipos devueltosReturn Types

Devuelve el tipo de datos de expresión con la prioridad de tipo de datos más alta.Returns the data type of expression with the highest data type precedence. Si ninguna de las expresiones admiten valores NULL, el resultado tiene un tipo que no admite valores NULL.If all expressions are nonnullable, the result is typed as nonnullable.

NotasRemarks

Si todos los argumentos son NULL, COALESCE devuelve NULL.If all arguments are NULL, COALESCE returns NULL. Al menos uno de los valores NULL debe ser NULL con tipo.At least one of the null values must be a typed NULL.

Comparar COALESCE y CASEComparing COALESCE and CASE

La expresión COALESCE es un método abreviado sintáctico de la expresión CASE.The COALESCE expression is a syntactic shortcut for the CASE expression. Es decir, el optimizador de consultas vuelve a escribir el código COALESCE(expresión1, ...n) como la expresión CASE siguiente:That is, the code COALESCE(expression1,...n) is rewritten by the query optimizer as the following CASE expression:

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.As such, the input values (expression1, expression2, expressionN, and so on) are evaluated multiple times. Una expresión de valor que contiene una subconsulta se considera no determinista, y la subconsulta se evalúa dos veces.A value expression that contains a subquery is considered non-deterministic and the subquery is evaluated twice. El resultado está conforme con el estándar SQL.This result is in compliance with the SQL standard. En cualquier caso, se pueden devolver resultados diferentes entre la primera evaluación y las evaluaciones posteriores.In either case, different results can be returned between the first evaluation and upcoming evaluations.

Por ejemplo, cuando se ejecuta el código COALESCE((subquery), 1), la subconsulta se evalúa dos veces.For example, when the code COALESCE((subquery), 1) is executed, the subquery is evaluated twice. En consecuencia, podrá obtener resultados diferentes en función del nivel de aislamiento de la consulta.As a result, you can get different results depending on the isolation level of the query. Por ejemplo, en un entorno multiusuario, el código puede devolver NULL en el nivel de aislamiento READ COMMITTED.For example, the code can return NULL under the READ COMMITTED isolation level in a multi-user environment. Para asegurarse de que se devuelven resultados estables, use el nivel de aislamiento SNAPSHOT ISOLATION, o bien reemplace COALESCE por la función ISNULL.To ensure stable results are returned, use the SNAPSHOT ISOLATION isolation level, or replace COALESCE with the ISNULL function. Como alternativa, puede volver a escribir la consulta para insertar la subconsulta en una subselección como se muestra en el ejemplo siguiente:As an alternative, you can rewrite the query to push the subquery into a subselect as shown in the following example:

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 ISNULLComparing COALESCE and ISNULL

La función ISNULL y la expresión COALESCE tienen una finalidad similar, pero se pueden comportar de forma diferente.The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.

  1. Dado que ISNULL es una función, solo se evalúa una vez.Because ISNULL is a function, it's evaluated only once. Como se describió anteriormente, los valores de entrada de la expresión COALESCE se pueden evaluar varias veces.As described above, the input values for the COALESCE expression can be evaluated multiple times.

  2. La determinación del tipo de datos de la expresión resultante es diferente.Data type determination of the resulting expression is different. ISNULL usa 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 con la prioridad más alta.ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

  3. La nulabilidad de la expresión de resultado es diferente para ISNULL y COALESCE.The NULLability of the result expression is different for ISNULL and COALESCE. El valor devuelto por ISNULL siempre se considera NOT NULL (suponiendo que el valor devuelto no admita valores NULL).The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one). Por el contrario, COALESCE con parámetros que no son NULL se considera NULL.By contrast,COALESCE with non-null parameters is considered to be NULL. Por tanto, aunque las expresiones ISNULL(NULL, 1) y COALESCE(NULL, 1) son equivalentes, tienen valores de nulabilidad diferentes.So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1), although equal, have different nullability values. Estos valores marcan la diferencia si estas expresiones se usan en columnas calculadas, se crean restricciones de clave o se hace que el valor devuelto de un UDF escalar sea determinista para poder indexarlo, como se muestra en el ejemplo siguiente:These values make a difference if you're using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example:

    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 de ISNULL y COALESCE también son diferentes.Validations for ISNULL and COALESCE are also different. Por ejemplo, con ISNULL, un valor NULL se convierte en int, mientras que con COALESCE es necesario proporcionar un tipo de datos.For example, a NULL value for ISNULL is converted to int though for COALESCE, you must provide a data type.

  5. ISNULL toma solo dos parámetros.ISNULL takes only two parameters. Por el contrario COALESCE toma un número variable de parámetros.By contrast COALESCE takes a variable number of parameters.

EjemplosExamples

A.A. Ejecutar un ejemplo sencilloRunning a simple example

En el ejemplo siguiente se muestra cómo COALESCE selecciona los datos de la primera columna que tiene un valor no nulo.The following example shows how COALESCE selects the data from the first column that has a nonnull value. En este ejemplo se usa la base de datos AdventureWorks2012AdventureWorks2012.This example uses the AdventureWorks2012AdventureWorks2012 database.

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

B.B. Ejecutar un ejemplo complejoRunning a complex example

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.In the following example, the wages table includes three columns that contain information about the yearly wages of the employees: the hourly wage, salary, and commission. No obstante, un empleado recibe solo un tipo de sueldo.However, an employee receives only one type of pay. 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.To determine the total amount paid to all employees, use COALESCE to receive only the nonnull value found in hourly_wage, salary, and 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.Here is the result set.

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 sencilloC: Simple Example

En el ejemplo siguiente se muestra cómo COALESCE selecciona los datos de la primera columna que tiene un valor que no es NULL.The following example demonstrates how COALESCE selects the data from the first column that has a non-null value. Para este ejemplo, se supone que la tabla Products contiene estos datos:Assume for this example that the Products table contains this data:

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

Después, se ejecuta la siguiente consulta COALESCE:We then run the following COALESCE query:

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

El conjunto de resultados es el siguiente.Here is the result set.

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 valor FirstNotNull es PN1278, no Socks, Mens.Notice that in the first row, the FirstNotNull value is PN1278, not Socks, Mens. Esto se debe a que en el ejemplo la columna Name no se especificó como un parámetro para COALESCE.This value is this way because the Name column wasn't specified as a parameter for COALESCE in the example.

D. Ejemplo complejoD: Complex Example

En el ejemplo siguiente se usa COALESCE para comparar los valores de tres columnas y devolver solo el valor distinto de NULL que se encuentra en las columnas.The following example uses COALESCE to compare the values in three columns and return only the non-null value found in the columns.

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.Here is the result set.

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

Consulte tambiénSee Also

ISNULL (Transact-SQL) ISNULL (Transact-SQL)
CASE (Transact-SQL)CASE (Transact-SQL)