COALESCE ( ) 정의
- COALESCE는 '합치다' 라는 뜻을 가진 단어다.
- SQL에서도 COALESCE() 함수는 입력받은 데이터를 하나로 합치는 기능을 한다.
- 합치는 기준은 'NULL'이다. 왼쪽부터 NULL이 아닌 첫번째 값을 반환함으로써 합친다. 즉, 왼쪽부터 'NULL'인지 아닌지를 따져서 NULL을 제외하고 처음 나오는 특정 값을 반환해 버리는 것이다. 단, 만약 모두 NULL 이라면 그냥 NULL을 내보낸다. (Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.)
사용 예시
1. COALESCE(value, ...)
-- (1)
SELECT COALESCE(NULL, 1); -- OUTPUT: 1
-- (2)
SELECT COALESCE(NULL, NULL, 'apple', 'cherry'); -- OUTPUT: 'apple'
-- (3)
SELECT COALESCE(NULL, NULL, NULL); -- OUTPUT: NULL
(1) 2개의 데이터를 입력받아 -> 1개로 합쳤다.
- 왼쪽부터 NULL을 제외하면 처음 나오는 값이 1이라서 1이 반환되었다.
(2) 4개의 데이터를 입력받아 -> 1개로 합쳤다.
- 왼쪽부터 NULL 제외, 그 다음도 NULL도 제외하면
- 처음 나오는 값이 'apple' 이라서 'apple' 이 반환되었다.
(3) 3개의 데이터를 입력받아 -> 1개로 합쳤다.
- NULL 밖에 없으니까 그냥 NULL이 반환되었다.
2. COALESCE(col1, col2, ... ) 또는 (col1, col2, ... , '기본값')
| id | nickname | username |
| 1 | NULL | john |
| 2 | kate | NULL |
| 3 | NULL | NULL |
-- (1)
SELECT id, COALESCE(nickname, username) AS name
FROM users
-- (2)
SELECT id, COALESCE(nickname, username, 'Guest') AS name
FROM users;
(1) 2개의 컬럼을 입력받아 -> 1개의 컬럼으로 합쳤다.
- 각 행(row)마다 왼쪽부터 순서대로 값 확인
| id | name |
| 1 | john |
| 2 | kate |
| 3 | NULL |
(2) 2개의 컬럼 + 1개의 텍스트를 입력받아 -> 1개의 컬럼으로 합쳤다.
- 각 행(row)마다 왼쪽부터 순서대로 값 확인
- 앞에 두 컬럼의 값이 모두 NULL인 경우, 마지막으로 입력받은 값(텍스트)이 반환된다.
- COALESCE()함수의 기능을 이용하여 테이블에서 NULL 인 값을 찾아 다른 값으로 변환하는데 사용할 수도 있다. 두 컬럼의 값이 NULL일 때 마지막으로 입력받은 값을 NULL을 대체하는 기본값으로 사용하는 것이다.
| id | name |
| 1 | john |
| 2 | kate |
| 3 | Guest |
정리
COALESCE 함수는 SQL 표준에 포함된 함수라서, MySQL뿐만 아니라 대부분의 메이저 DBMS에서 동일한 방식으로 사용 가능하다.
[참고] COALESCE 지원 여부 (DBMS별 비교)
| DBMS | COALESCE 지원 여부 | 메모 |
| MySQL | ✅ 지원 | IFNULL()도 있으나 COALESCE가 더 범용 |
| PostgreSQL | ✅ 지원 | 널 대체뿐만 아니라 연산에도 자주 활용 |
| SQL Server | ✅ 지원 | ISNULL()도 있으나 COALESCE는 표준 |
| Oracle | ✅ 지원 | NVL()도 있지만 COALESCE 더 유연함 |
| SQLite | ✅ 지원 | 기본 지원 |
| BigQuery | ✅ 지원 | 사용 가능 |
[참고] NULL 관련 함수
| 함수 | 목적 | 동작 방식 |
| COALESCE(a, b, c) | 왼쪽부터 NULL이 아닌 첫 값 반환 | a, b, c 중에서 첫 번째로 NULL이 아닌 값 반환 [ 예시 ] COALESCE(NULL, NULL, 'abc') → 'abc' |
| IFNULL(a, b) | a가 NULL이면 b 반환 | MySQL 스타일 (2개 인자) [ 예시 ] IFNULL(NULL, 0) → 0 |
| NULLIF(a, b) | a와 b가 같으면 NULL 반환, 다르면 a 반환 | 나누기 등에서 0 피할 때 유용 [ 예시 ] NULLIF(100, 100) → NULL NULLIF(100, 0) → 100 |
| ISNULL(a) | a가 NULL인지 여부 (True/False) | 일부 DB에서는 IF(ISNULL(col), ...) 형태로 활용 [ 예시 ] ISNULL(NULL) → True |