COALESCE(Transact-SQL)

아티클
03/07/2014

인수를 순서대로 평가하고 처음으로 Null이 아닌 첫 번째 식의 현재 값을 반환합니다.

적용 대상: SQL Server(SQL Server 2008 - current version), Windows Azure SQL 데이터베이스(최초 릴리스 - 현재 릴리스)

구문

COALESCE ( expression [ ,...n ] )

인수

expression
모든 형식의 식입니다.

반환 형식

데이터 형식 우선 순위가 가장 높은 expression의 데이터 형식을 반환합니다. 모든 식에서 Null을 허용하지 않으면 결과가 Null을 허용하지 않는 형식으로 처리됩니다.

주의

모든 인수가 NULL이면 COALESCE는 NULL을 반환합니다. Null 값을 적어도 하나 이상 입력해야 합니다.

COALESCE 및 CASE 비교

COALESCE 식은 CASE 식의 구문 바로 가기입니다. 즉, 쿼리 최적화 프로그램에서는 COALESCE(expression1,...n) 코드를 다음과 같은 CASE 식으로 다시 작성합니다.

CASE

WHEN (expression1 IS NOT NULL) THEN expression1

WHEN (expression2 IS NOT NULL) THEN expression2

...

ELSE expressionN

END

이는 입력 값(expression1, expression2, expressionN 등)이 여러 번 평가됨을 의미합니다. 또한 SQL 표준에 따라 하위 쿼리가 포함된 값 식은 비결정적인 것으로 간주되어 하위 쿼리가 두 번 평가됩니다. 어느 경우에든 첫 번째 평가와 이후 평가 간에 서로 다른 결과가 반환될 수 있습니다.

예를 들어 COALESCE((subquery), 1) 코드를 실행하면 하위 쿼리가 두 번 평가됩니다. 따라서 쿼리 격리 수준에 따라 다른 결과가 반환될 수 있습니다. 예를 들어 다중 사용자 환경의 READ COMMITTED 격리 수준에서는 이 코드에서 Null을 반환할 수 있습니다. 일정한 결과가 반환되도록 하려면 SNAPSHOT ISOLATION 격리 수준을 사용하거나 COALESE를 ISNULL 함수로 바꿔야 합니다. 또는 다음 예와 같이 하위 쿼리가 하위 SELECT 안에 들어가도록 쿼리를 다시 작성할 수 있습니다.

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 비교

ISNULL 함수와 COALESCE 식은 용도는 비슷하지만 동작은 다를 수 있습니다.

ISNULL은 함수이므로 한 번만 평가됩니다. 위의 설명대로 COALESCE 식의 입력 값은 여러 번 평가될 수 있습니다.
결과 식의 데이터 형식 결정 방법이 다릅니다. ISNULL은 첫 번째 매개 변수의 데이터 형식을 사용하고 COALESCE는 CASE 식 규칙에 따라 우선 순위가 가장 높은 값의 데이터 형식을 반환합니다.
ISNULL과 COALESCE는 결과 식에서의 Null 허용 여부가 다릅니다. ISNULL 반환 값은 항상 Null을 허용하지 않는 것으로 간주(반환 값이 Null을 허용하지 않는 값이라고 가정)되는 반면 Null이 아닌 매개 변수가 있는 COALESCE는 Null로 간주됩니다. 따라서 ISNULL(NULL, 1)이라는 식과 COALESCE(NULL, 1)라는 식은 동일하지만 Null 허용 여부 값이 다릅니다. 이 때문에 이러한 식을 계산 열에서 사용하거나, 키 제약 조건을 만들거나, 다음 예에서와 같이 인덱싱이 가능하도록 스칼라 UDF 결정적 식의 반환 값을 만드는 경우 차이가 발생합니다.
```
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 
);
```
ISNULL과 COALESCE에 대한 유효성 검사도 다릅니다. 예를 들어 ISNULL은 Null 값이 int로 변환되는 반면 COALESCE의 경우 데이터 형식을 직접 제공해야 합니다.
ISNULL은 매개 변수를 2개만 사용하는 반면 COALESCE가 사용하는 매개 변수의 수는 가변적입니다.

예

1.간단한 예 실행

다음 예에서는 COALESCE가 Null 이외의 값이 있는 첫 번째 열에서 데이터를 선택하는 방법을 보여 줍니다. 다음 예에서는 AdventureWorks2012 데이터베이스를 사용합니다.

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

2.복잡한 예 실행

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

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

결과 집합은 다음과 같습니다.

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)

참고 항목

참조

ISNULL(Transact-SQL)

CASE(Transact-SQL)

다음을 통해 공유