COALESCE(Transact-SQL)
순서대로 인수를 계산하고, NULL로 계산되지 않는 첫 번째 식의 현재 값을 반환합니다.
적용 대상: SQL Server(SQL Server 2008 - current version), Windows Azure SQL 데이터베이스(최초 릴리스 - 현재 릴리스) |
.gif "항목 링크 아이콘")
구문
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 식 규칙을 따라 우선 순위가 가장 높은 값의 데이터 형식을 반환합니다.
결과 식의 Null 허용 여부는 ISNULL인지 COALESCE인지에 따라 다릅니다. 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은 두 개의 매개 변수만 사용하는 반면 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)