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

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure simSQL Data Warehouse do Azure simParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Avalia os argumentos na ordem e retorna o valor atual da primeira expressão que não é avaliada como NULL inicialmente.Evaluates the arguments in order and returns the current value of the first expression that initially doesn't evaluate to NULL. Por exemplo, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); retorna o terceiro valor porque esse é o primeiro valor que não é nulo.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.

Ícone de link do tópico Convenções de sintaxe de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxeSyntax

COALESCE ( expression [ ,...n ] )   

ArgumentosArguments

expressãoexpression
É uma expressão de qualquer tipo.Is an expression of any type.

Tipos de retornoReturn Types

Retorna o tipo de dados de expressão com a maior precedência de tipo de dados.Returns the data type of expression with the highest data type precedence. Se todas as expressões forem não anuláveis (nonnullable), o resultado será digitado como nonnullable.If all expressions are nonnullable, the result is typed as nonnullable.

RemarksRemarks

Se todos os argumentos forem NULL, COALESCE retornará NULL.If all arguments are NULL, COALESCE returns NULL. Pelo menos um dos valores nulos precisa ser do tipo NULL.At least one of the null values must be a typed NULL.

Comparando COALESCE e CASEComparing COALESCE and CASE

A expressão COALESCE é um atalho sintático para a expressão CASE.The COALESCE expression is a syntactic shortcut for the CASE expression. Ou seja, o código COALESCE(expression1, ...n) é reescrito pelo otimizador de consulta como a seguinte expressão CASE: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  

Dessa forma, os valores de entrada (expression1, expression2, expressionN e assim por diante) são avaliados várias vezes.As such, the input values (expression1, expression2, expressionN, and so on) are evaluated multiple times. Uma expressão de valor que contém uma consulta aninhada é considerada não determinística e a consulta aninhada é avaliada duas vezes.A value expression that contains a subquery is considered non-deterministic and the subquery is evaluated twice. Esse resultado está em conformidade com o padrão SQL.This result is in compliance with the SQL standard. Em ambos os casos, resultados diferentes podem ser retornados entre a primeira avaliação e as avaliações posteriores.In either case, different results can be returned between the first evaluation and upcoming evaluations.

Por exemplo, quando o código COALESCE((subquery), 1) é executado, a subconsulta é avaliada duas vezes.For example, when the code COALESCE((subquery), 1) is executed, the subquery is evaluated twice. Como resultado, você pode obter resultados diferentes dependendo do nível de isolamento da consulta.As a result, you can get different results depending on the isolation level of the query. Por exemplo, o código pode retornar NULL no nível de isolamento READ COMMITTED em um ambiente multiusuário.For example, the code can return NULL under the READ COMMITTED isolation level in a multi-user environment. Para assegurar que resultados estáveis sejam retornados, use o nível de isolamento SNAPSHOT ISOLATION ou substitua COALESCE pela função ISNULL.To ensure stable results are returned, use the SNAPSHOT ISOLATION isolation level, or replace COALESCE with the ISNULL function. Como alternativa, você pode reescrever a consulta para enviar a consulta aninhada por push para uma seleção aninhada, conforme mostrado no exemplo a seguir: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;  
  

Comparando COALESCE e ISNULLComparing COALESCE and ISNULL

A função ISNULL e a expressão COALESCE têm uma finalidade semelhante, mas podem ter um comportamento diferente.The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.

  1. Como ISNULL é uma função, ela é avaliada apenas uma vez.Because ISNULL is a function, it's evaluated only once. Conforme descrito acima, a entrada de valores para a expressão COALESCE pode ser avaliada várias vezes.As described above, the input values for the COALESCE expression can be evaluated multiple times.

  2. A determinação de tipo de dados da expressão resultante é diferente.Data type determination of the resulting expression is different. ISNULL usa o tipo de dados do primeiro parâmetro, COALESCE segue as regras da expressão CASE e retorna o tipo de dados de valor com a precedência mais 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. A nulidade da expressão resultante é diferente para ISNULL e COALESCE.The NULLability of the result expression is different for ISNULL and COALESCE. O valor retornado ISNULL sempre é considerado como um valor que não permite valor nulo (supondo que o valor retornado não permita valor nulo).The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one). Por outro lado,COALESCE com parâmetros não nulos é considerado NULL.By contrast,COALESCE with non-null parameters is considered to be NULL. Portanto, as expressões ISNULL(NULL, 1) e COALESCE(NULL, 1), embora equivalentes, têm valores diferentes de nulidade.So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1), although equal, have different nullability values. Esses valores farão diferença se você estiver usando essas expressões em colunas computadas, criando restrições de chave ou tornando determinístico o valor retornado de um UDF escalar para que ele possa ser indexado, como mostrado no exemplo a seguir: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. As validações de ISNULL e de COALESCE também são diferentes.Validations for ISNULL and COALESCE are also different. Por exemplo, um valor NULL para ISNULL é convertido em int, já para COALESCE, você precisa fornecer um tipo de dados.For example, a NULL value for ISNULL is converted to int though for COALESCE, you must provide a data type.

  5. ISNULL utiliza apenas dois parâmetros.ISNULL takes only two parameters. Por outro lado, COALESCE usa um número variável de parâmetros.By contrast COALESCE takes a variable number of parameters.

ExemplosExamples

A.A. Executando um exemplo simplesRunning a simple example

O exemplo a seguir mostra como COALESCE seleciona os dados da primeira coluna que tem um valor não nulo.The following example shows how COALESCE selects the data from the first column that has a nonnull value. Este exemplo usa o banco de dados AdventureWorks2012AdventureWorks2012.This example uses the AdventureWorks2012AdventureWorks2012 database.

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

B.B. Executando um exemplo complexoRunning a complex example

No exemplo a seguir, a tabela wages inclui três colunas que contêm informações sobre o salário anual dos funcionários: valor por hora, salário e comissão.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 entanto, um funcionário recebe apenas um tipo de pagamento.However, an employee receives only one type of pay. Para determinar o valor total pago a todos os funcionários, use a função COALESCE para receber apenas o valor não nulo encontrado em hourly_wage, salary e 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  

Aqui está o conjunto de resultados.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: Exemplo simplesC: Simple Example

O exemplo a seguir demonstra como COALESCE seleciona os dados da primeira coluna que tem um valor não nulo.The following example demonstrates how COALESCE selects the data from the first column that has a non-null value. Considere para este exemplo que a tabela Products contém estes dados: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

Em seguida, executaremos a seguinte consulta COALESCE:We then run the following COALESCE query:

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

Aqui está o conjunto de resultados.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 na primeira linha, o valor de FirstNotNull é PN1278, não Socks, Mens.Notice that in the first row, the FirstNotNull value is PN1278, not Socks, Mens. Esse valor funciona assim porque a coluna Name não foi especificada como um parâmetro para COALESCE no exemplo.This value is this way because the Name column wasn't specified as a parameter for COALESCE in the example.

D: Exemplo complexoD: Complex Example

O exemplo a seguir usa COALESCE para comparar os valores em três colunas e retornar apenas o valor não nulo encontrado nas colunas.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;  

Aqui está o conjunto de resultados.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 TambémSee Also

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