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

적용 대상: 예SQL Server 예Azure SQL Database 예Azure SQL Data Warehouse 예병렬 데이터 웨어하우스 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

인수를 순서대로 평가하고 처음으로 NULL이 아닌 첫 번째 식의 현재 값을 반환합니다.Evaluates the arguments in order and returns the current value of the first expression that initially doesn't evaluate to NULL. 예를 들어 SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value');는 세 번째 값이 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.

항목 링크 아이콘 Transact-SQL 구문 규칙Topic link icon Transact-SQL Syntax Conventions

구문Syntax

COALESCE ( expression [ ,...n ] )   

인수Arguments

expressionexpression
모든 형식의 입니다.Is an expression of any type.

반환 형식Return Types

데이터 형식 우선 순위가 가장 높은 _식_의 데이터 형식을 반환합니다.Returns the data type of expression with the highest data type precedence. 모든 식에서 Null을 허용하지 않으면 결과가 Null을 허용하지 않는 형식으로 처리됩니다.If all expressions are nonnullable, the result is typed as nonnullable.

RemarksRemarks

모든 인수가 NULL인 경우 COALESCENULL를 반환합니다.If all arguments are NULL, COALESCE returns NULL. Null 값 중 하나 이상이 NULL 형식이어야 합니다.At least one of the null values must be a typed NULL.

COALESCE 및 CASE 비교Comparing COALESCE and CASE

COALESCE 식은 CASE 식의 구문 바로 가기입니다.The COALESCE expression is a syntactic shortcut for the CASE expression. 즉, 쿼리 최적화 프로그램에서는 COALESCE(expression1, ...n) 코드를 다음과 같은 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  

입력 값(expression1, expression2, expressionN 등)이 여러 번 평가 됩니다.As such, the input values (expression1, expression2, expressionN, and so on) are evaluated multiple times. 하위 쿼리가 포함된 값 식은 비결정적인 것으로 간주되어 하위 쿼리가 두 번 평가됩니다.A value expression that contains a subquery is considered non-deterministic and the subquery is evaluated twice. 이 결과는 SQL 표준을 준수합니다.This result is in compliance with the SQL standard. 어느 경우에든 첫 번째 평가와 예정된 평가 간에 서로 다른 결과가 반환될 수 있습니다.In either case, different results can be returned between the first evaluation and upcoming evaluations.

예를 들어 COALESCE((subquery), 1) 코드를 실행하면 하위 쿼리가 두 번 평가됩니다.For example, when the code COALESCE((subquery), 1) is executed, the subquery is evaluated twice. 따라서 쿼리 격리 수준에 따라 다른 결과가 반환될 수 있습니다.As a result, you can get different results depending on the isolation level of the query. 예를 들어 다중 사용자 환경의 READ COMMITTED 격리 수준에서는 이 코드에서 NULL을 반환할 수 있습니다.For example, the code can return NULL under the READ COMMITTED isolation level in a multi-user environment. 일정한 결과가 반환되도록 하려면 SNAPSHOT ISOLATION 격리 수준을 사용하거나 ISNULL 함수로 COALESCE를 바꿔야 합니다.To ensure stable results are returned, use the SNAPSHOT ISOLATION isolation level, or replace COALESCE with the ISNULL function. 또는 다음 예와 같이 하위 쿼리가 하위 선택 안에 들어가도록 쿼리를 다시 작성할 수 있습니다.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;  
  

COALESCE 및 ISNULL 비교Comparing COALESCE and ISNULL

ISNULL 함수와 COALESCE 식은 용도는 비슷하지만 동작은 다를 수 있습니다.The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.

  1. ISNULL은 함수이므로 한 번만 평가됩니다.Because ISNULL is a function, it's evaluated only once. 위의 설명대로 COALESCE 식의 입력 값은 여러 번 평가될 수 있습니다.As described above, the input values for the COALESCE expression can be evaluated multiple times.

  2. 결과 식의 데이터 형식 결정 방법이 다릅니다.Data type determination of the resulting expression is different. ISNULL은 첫 번째 매개 변수의 데이터 형식을 사용하고 COALESCECASE 식 규칙에 따라 우선 순위가 가장 높은 값의 데이터 형식을 반환합니다.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. ISNULLCOALESCE는 결과 식에서의 NULL 허용 여부가 다릅니다.The NULLability of the result expression is different for ISNULL and COALESCE. ISNULL 반환 값은 항상 Null을 허용하지 않는 것으로 간주(반환 값이 Null을 허용하지 않는 값이라고 가정)됩니다.The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one). 이와 반대로 Null이 아닌 매개 변수가 있는 COALESCENULL로 간주합니다.By contrast,COALESCE with non-null parameters is considered to be NULL. 따라서 ISNULL(NULL, 1)COALESCE(NULL, 1) 식은 같지만 Null 허용 여부 값이 다릅니다.So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1), although equal, have different nullability values. 이 값을 사용하면 해당 식을 계산 열에서 사용하거나, 키 제약 조건을 만들거나, 다음 예에서와 같이 인덱싱이 가능하도록 스칼라 UDF 결정적 식의 반환 값을 만드는 경우 차이가 발생합니다.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. ISNULLCOALESCE에 대한 유효성 검사도 다릅니다.Validations for ISNULL and COALESCE are also different. 예를 들어 COALESCE의 경우 데이터 형식을 직접 제공해야 하지만 ISNULL에 대한 NULL 값은 int로 변환됩니다.For example, a NULL value for ISNULL is converted to int though for COALESCE, you must provide a data type.

  5. ISNULL에는 다음 두 개의 매개 변수만 사용됩니다.ISNULL takes only two parameters. 반대로, COALESCE가 사용하는 매개 변수의 수는 가변적입니다.By contrast COALESCE takes a variable number of parameters.

Examples

1.A. 간단한 예 실행Running a simple example

다음 예에서는 COALESCE가 Null 이외의 값이 있는 첫 번째 열에서 데이터를 선택하는 방법을 보여 줍니다.The following example shows how COALESCE selects the data from the first column that has a nonnull value. 다음 예에서는 AdventureWorks2012AdventureWorks2012 데이터베이스를 사용합니다.This example uses the AdventureWorks2012AdventureWorks2012 database.

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

2.B. 복잡한 예 실행Running a complex example

다음 예에서는 wages 테이블에 직원의 연봉 정보에 대한 시급, 월급 및 커미션의 3개 열이 포함되어 있습니다.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. 그러나 각 직원은 이 중 한 종류의 급여만 받습니다.However, an employee receives only one type of pay. 모든 직원에게 지급된 총 급여액을 확인하려면 COALESCE 함수를 사용하여 hourly_wage, salary, commission에서 검색된 Null이 아닌 값만 포함시킵니다.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  

결과 집합은 다음과 같습니다.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: 간단한 예C: Simple Example

다음 예에서는 COALESCE가 Null 이외의 값이 있는 첫째 열에서 데이터를 선택하는 방법을 보여 줍니다.The following example demonstrates how COALESCE selects the data from the first column that has a non-null value. 이 예제에서는 Products 테이블이 이 데이터를 포함한다고 가정합니다.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

이 후 다음 COALESCE 쿼리를 실행합니다.We then run the following COALESCE query:

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

결과 집합은 다음과 같습니다.Here is the result set.

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

첫 번째 행에서의 FirstNotNull 값은 Socks, Mens가 아닌 PN1278입니다.Notice that in the first row, the FirstNotNull value is PN1278, not Socks, Mens. 예제에서 Name 열이 COALESCE에 대한 매개 변수로 지정되지 않았기 때문에 이와 같은 값이 사용됩니다.This value is this way because the Name column wasn't specified as a parameter for COALESCE in the example.

D: 복잡한 예D: Complex Example

다음 예제에서는 COALESCE를 사용하여 세 열의 값을 비교하고 열에서 찾은 Null이 아닌 값만 반환합니다.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;  

결과 집합은 다음과 같습니다.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

참고 항목See Also

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