COALESCE(Transact-SQL)

아티클
01/29/2025

적용 대상:Microsoft Fabric의 Microsoft Fabric SQL 데이터베이스에 있는 Microsoft Fabric Warehouse의 SQL Server Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System(PDW)SQL 분석 엔드포인트

인수를 순서대로 평가하고 처음으로 NULL이 아닌 첫 번째 식의 현재 값을 반환합니다. 다음은 세 번째 값이 null이 아닌 첫 번째 값이기 때문에 세 번째 값을 반환하는 예제입니다.

SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value');

메모

문자열을 연결하려면 대신 STRING_AGG 사용합니다.

Transact-SQL 구문 표기 규칙

구문

COALESCE ( expression [ , ...n ] )

인수

expression

모든 형식의 식.

반환 형식

데이터 형식 우선 순위가 가장 높은 식의 데이터 형식을 반환합니다. 모든 식이 null을 허용하지 않는 경우 결과는 null을 허용하지 않는 형식으로 입력됩니다.

설명

모든 인수가 NULL인 경우 COALESCE가 NULL를 반환합니다. 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) 코드를 실행하면 하위 쿼리가 두 번 평가됩니다. 따라서 쿼리 격리 수준에 따라 다른 결과가 반환될 수 있습니다. 예를 들어 다중 사용자 환경의 NULL 격리 수준에서는 이 코드에서 READ COMMITTED을 반환할 수 있습니다. 일정한 결과가 반환되도록 하려면 SNAPSHOT ISOLATION 격리 수준을 사용하거나 COALESCE 함수로 ISNULL를 바꿔야 합니다. 또는 다음 예와 같이 하위 쿼리가 하위 선택 안에 들어가도록 쿼리를 다시 작성할 수 있습니다.

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 첫 번째 매개 변수의 데이터 형식을 사용하고 COALESCECASE 식 규칙을 따라 우선 순위가 가장 높은 값의 데이터 형식을 반환합니다.
ISNULL 및 COALESCE는 결과 식에서의 NULL 허용 여부가 다릅니다. ISNULL 반환 값은 항상 NOT NULL수 있는 것으로 간주됩니다(반환 값이 nullable이 아닌 값이라고 가정). 이와 반대로 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 INT 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 INT NULL,
    col2 AS COALESCE (col1, 0),
    col3 AS ISNULL(col1, 0) PRIMARY KEY
);
```
ISNULL 및 COALESCE에 대한 유효성 검사도 다릅니다. 예를 들어 NULL의 경우 데이터 형식을 직접 제공해야 하지만 ISNULL에 대한 값은 COALESCE로 변환됩니다.
ISNULL에는 다음 두 개의 매개 변수만 사용됩니다. 반대로, COALESCE가 사용하는 매개 변수의 수는 가변적입니다.

예

이 문서의 코드 샘플은 AdventureWorks2022 또는 AdventureWorksDW2022 샘플 데이터베이스를 사용합니다. 이 데이터베이스는 Microsoft SQL Server 샘플 및 커뮤니티 프로젝트 홈페이지에서 다운로드할 수 있습니다.

A. null이 아닌 값이 있는 첫 번째 열에서 데이터를 반환합니다.

다음 예에서는 COALESCE가 Null 이외의 값이 있는 첫째 열에서 데이터를 선택하는 방법을 보여 줍니다. 이 예제에서는 Products 테이블이 이 데이터를 포함한다고 가정합니다.

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

그런 다음, 다음 COALESCE 쿼리를 실행합니다.

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

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

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

첫 번째 행에서 FirstNotNull 값은 Socks, Mens아니라 PN1278. 예제에서 Name 열이 COALESCE에 대한 매개 변수로 지정되지 않았기 때문에 이와 같은 값이 사용됩니다.

B. wages 테이블에서 null이 아닌 값을 반환합니다.

다음 예에서는 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

다음을 통해 공유

COALESCE(Transact-SQL)

구문

인수

expression

반환 형식

설명

COALESCE 및 CASE 비교

COALESCE 및 ISNULL 비교

예

A. null이 아닌 값이 있는 첫 번째 열에서 데이터를 반환합니다.

B. wages 테이블에서 null이 아닌 값을 반환합니다.

피드백

추가 리소스

다음을 통해 공유

COALESCE(Transact-SQL)

구문

인수

expression

반환 형식

설명

COALESCE 및 CASE 비교

COALESCE 및 ISNULL 비교

예

A. null이 아닌 값이 있는 첫 번째 열에서 데이터를 반환합니다.

B. wages 테이블에서 null이 아닌 값을 반환합니다.

관련 콘텐츠

피드백

추가 리소스