SQL COALESCE 함수와 NULL값의 만남
여러분은 이미 SQL에서 NULL값을 반환하는 방법을 아실 수도 있습니다. 이제는 역으로 NULL값을 반환하지 않는 방법에 대해 살펴보려고 합니다. 비록 SQL 내 COALESCE 함수가 복잡해 보일 수는 있어도, 막상 파헤쳐보면 꽤나 직관적이고 유용한 함수랍니다. 몇 가지 예시를 통해 해당 함수가 NULL 값을 만나면 어떤 일이 생기는지 살펴보도록 하죠.
SQL에서 COALESCE 함수가 필요한 이유
해당 함수에 대한 본격적인 이야기를 시작하기 전에 NULL 값에 대해 먼저 이야기하고자 합니다. 간단히 말해서, NULL 값은 데이터가 존재하지 않는다는 것을 의미합니다(이는 숫자 0과 다른 의미임). NULL 값과 그 어떤 다른 값(i.e. 숫자, 문자, 날짜, 참/거짓의 boolean 등)을 사칙연산을 해도 그 결과는 NULL 값입니다. 그 이유는 NULL 값은 데이터가 존재하지 않아 알 수 없는 값이기 때문에, 알 수 없는 값과 특정 값을 더하고, 빼고, 곱하고, 나누더라도 여전히 알 수 없는 값이기 때문이죠.
아래는 NULL 값과 다른 데이터 종류를 사칙연산할 시 어떤 결과가 나오는지를 보여줍니다.
(1) 숫자 + NULL : 2 + NULL = NULL
(2) 숫자 * NULL : 2 * NULL = NULL
(3) 문자 + NULL : 'hello' II NULL = NULL
(4) 날짜 + NULL : interval '1 day' + NULL = NULL
(5) Boolean + NULL : TRUE and NULL = NULL
교통 위반금 데이터를 통해 개념 이해하기
COALESCE 함수의 작동 원리를 설명하고자 교통 위반금 데이터를 예로 들고자 합니다. 아래 테이블을 보면 운전자의 ID, 그들이 받은 위반금, 위반금이 징수된 날짜와 시간, 교통 위반 행위의 수위, 미납된 교통 위반금에 대한 정보가 저장되어 있습니다.
| driver_id | fine_value | reason | offense_level | offense_rate | date_and_time | unpaid_fines |
| 16877165 | 150.00 | Speeding | Band B | 1.5 | 2011/06/26 08:03 AM | |
| 18314338 | 500.00 | Red Light | Band C | 2.5 | 2011/06/26 08:07 AM | 5405.14 |
| 26177418 | 150.00 | Speeding | Band B | 1.5 | 2011/06/25 11:07 AM | 200.00 |
| 41681615 | 100.00 | Stop Sign | 2011/06/26 09:43 AM | 0.00 | ||
| 41681615 | 100.00 | Stop Sign | Band A | 1 | 2011/06/27 10:23 AM | 0.00 |
이 중에서 offense_level 열을 유심히 봐주세요. 해당 열은 'Band A', 'Band B', 'Band C'의 값을 가질 수 있습니다. Band B는 30일 이내 위반금을 내지 않으면 30일 이후 기존 위반금의 1.5배를 지불해야 합니다. Band C의 경우는 기간 내 위반금을 내지 않으면 기존 위반금의 2배를 지불해야 합니다. Band A는 30일이 지나도 지불해야 하는 위반금의 액수에는 변동이 없습니다.
숫자 + NULL 은? NULL!
우선 30일 이내 위반금을 낸다는 가정 하에 이전 미납금과 이번에 부과될 위반금을 합친 총액을 계산하고자 합니다. 아래 쿼리를 실행하면 각 운전자의 아이디와, 이번에 부과될 위반금(fine_value), 그리고 이번에 부과될 위반금과 이전 미납금(unpaid_fines)을 합친 총 위반금을 보여줄 것입니다.
SELECT
driver_id,
fine_value AS fine_amount,
fine_value + unpaid_fines AS total_due
FROM fines;
하지만, 결과를 살펴보면 첫 번째 행의 총 위반금 금액이 아무것도 없는 것을 확인할 수 있습니다. 왜 그런걸까요?
| driver_id | fine_amount | total_due |
| 16877165 | 150.00 | |
| 18314338 | 500.00 | 5905.14 |
| 26177418 | 150.00 | 350.00 |
| 41681615 | 100.00 | 100.00 |
| 41681615 | 100.00 | 100.00 |
그 이유는 꽤나 간단합니다. 운전자 아이디가 16877165인 사람은 이전 미납금이 존재하지 않기 때문인데요. 즉 이전 미납금이 NULL 값이기 때문입니다. 위에서도 언급했듯이, NULL 값과 그 어떤 값을 사칙연산하더라도 NULL 값을 반환한다는 사실 기억하시죠? 그래서 이번에 부과할 위반금(150)과 이전 미납금(NULL)을 더했을 때 NULL 값이 나와서 결과 테이블 내의 셀에는 아무것도 존재하지 않는 것입니다.
COALESCE 함수를 이용해 NULL 값을 다른 값으로 치환하기
위의 문제를 해결하고자 우리는 COALESCE 함수를 사용할 것입니다. 이 함수는 데이터가 NULL 값이면 해당 NULL 값을 괄호 안에 있는 값의 순서대로 치환해줍니다. 괄호 안에 적을 수 있는 값의 개수는 무한합니다. 아래 예시에서는 우리는 딱 2개의 치환 값만 적을 건데요. 바로 미납금과 0입니다. 쿼리를 한 번 보시죠.
SELECT
driver_id,
fine_value AS fine_amount,
fine_value + COALESCE(unpaid_fines, 0) AS total_due
FROM fines;
| driver_id | fine_amount | total_due |
| 16877165 | 150.00 | 150.00 |
| 18314338 | 500.00 | 5905.14 |
| 26177418 | 150.00 | 350.00 |
| 41681615 | 100.00 | 100.00 |
| 41681615 | 100.00 | 100.00 |
어떻게 해서 위와 같은 결과가 나오게 되었는지 살펴볼까요?
COALESCE(unpaid_fines, 0)이 실행되는 순서는 다음과 같습니다. 만약 unpaid_fines가 NULL 값이면, SQL은 괄호 안의 그다음 값인 0을 반환해 냅니다. 여기서 unpaid_fines가 NULL 값이라는 건 미납금이 0원이라는 말이겠지요. 그래서 괄호 안에 0이라는 숫자를 사용했답니다. 미납금을 NULL 값 대신 0원으로 치환해야 이번에 부과될 위반금과 이전 미납금을 더했을 때 제대로 된 위반금 총액을 구할 수 있을 테니까요.
만약 unpaid_fines 가 NULL 값이 아니라면 COALESCE 함수는 거기서 실행을 멈추고 unpaid_fines의 값을 반환해 냅니다. NULL 값을 맞닥뜨린 것이 아니니 이를 치환할 다른 값을 볼 필요도, 불러 올 필요도 없는 것이죠.
기간 내 위반금을 납부하지 못한 경우는?
이 글의 초반에서 30일 이내에 위반금을 납부하지 않으면, 그 이후의 납부금은 더 커졌던 점 기억하시나요? 이번에 부과될 위반금(fine_value)에다가 미납 수수료 비율(offense_rate)을 곱해주면 됩니다. 아래 쿼리에서 COALESCE 함수를 사용해 30일 이후의 위반금을 계산하고자 합니다. 한 번 보시죠.
SELECT
driver_id,
fine_value AS fine_amount,
COALESCE(unpaid_fines, 0) AS unpaid_fines,
fine_value + COALESCE(unpaid_fines, 0) AS total_due,
fine_value * offense_rate + COALESCE(unpaid_fines, 0) AS total_due_after_30_days
FROM fines;
| driver_id | fine_amount | unpaid_fines | total_due | total_due_after_30_days |
| 16877165 | 150.00 | 0.00 | 150.00 | 225.00 |
| 18314338 | 500.00 | 5405.14 | 5905.14 | 6405.14 |
| 26177418 | 150.00 | 200.00 | 350.00 | 425.00 |
| 41681615 | 100.00 | 0.00 | 100.00 | |
| 41681615 | 100.00 | 0.00 | 100.00 | 100.00 |
이번에는 total_due_after_30_days 열의 4번째 행 값이 존재하지 않네요. 그 이유는 무엇일까요? 그건 바로 6월 26일 운전자 아이디 41681615의 offense_rate가 NULL 값이기 때문입니다. 그렇다면 NULL 값을 다른 값으로 치환하기 위해서는 어떻게 해야 할까요? 그죠, COALESCE 함수를 사용하면 됩니다. 이 데이터를 다루는 부서가 offense_rate가 정의되지 않은 경우 30일 이전 위반금과 30일 이후 위반금 간의 액수 변동이 없는 Band A(즉, 미납금은 기본 위반금의 1배)처럼 처리하기로 했다고 가정해봅시다. 그러면 우리는 COALESCE(offense_rate, 1)이라고 적어주면 됩니다. 이렇게 하면, offense_rate가 NULL 값이 아닌 것은 해당 offense_rate를 반환할 것이고, NULL 값이면 이를 1로 치환해 줄 것입니다.
아래는 지금까지의 설명을 반영한 쿼리입니다.
SELECT
driver_id,
fine_value AS fine_amount,
COALESCE(unpaid_fines, 0) AS unpaid_fines,
fine_value + COALESCE(unpaid_fines, 0) AS total_due,
fine_value * COALESCE(offense_rate, 1) + COALESCE(unpaid_fines, 0) AS total_due_after_30_days
FROM fines;
| driver_id | fine_amount | unpaid_fines | total_due | total_due_after_30_days |
| 16877165 | 150.00 | 0.00 | 150.00 | 225.00 |
| 18314338 | 500.00 | 5405.14 | 5905.14 | 6405.14 |
| 26177418 | 150.00 | 200.00 | 350.00 | 425.00 |
| 41681615 | 100.00 | 0.00 | 100.00 | 100.00 |
| 41681615 | 100.00 | 0.00 | 100.00 | 100.00 |
마지막 예시로 살펴보고자 하는 것은 문자와 문자를 합칠 때, 하나의 값이 NULL 값일 때입니다. 아래 쿼리를 살펴보세요.
SELECT
driver_id,
reason II ' - ' II offense_level AS reason_and_severity,
fine_value + COALESCE(unpaid_fines, 0) AS total_due
FROM fines;
* CONCAT 함수: 문자열을 연결하는 함수. CONCAT 함수 대신 II 연산자를 사용해도 됨. [예시] CONCAT(데이터,분석,가) = 데이터분석가
| driver_id | reason_and_severity | total_due |
| 16877165 | Speeding – Band B | 150.00 |
| 18314338 | Red Light – Band C | 5905.14 |
| 26177418 | Speeding – Band B | 350.00 |
| 41681615 | 100.00 | |
| 41681615 | Stop Sign – Band A | 100.00 |
reason_and_severity 열의 4번째 행을 보니 값이 존재하질 않습니다. 그 이유는 무엇입니까? 바로 6월 26일 운전자 아이디 41681615의 offense_level이 NULL 값이기 때문입니다. 문자열에 NULL을 더했으니 NULL일 수밖에요! 그래서 우린 COALESCE 함수를 사용해 해당 NULL 값을 'No Band'으로 치환해 달라고 부탁할 겁니다. 이를 반영해 쿼리를 작성하면 아래와 같습니다. 쿼리 실행 결과도 함께 살펴보세요. 모든 데이터가 빈 행 없이 잘 반환된 것을 보실 수 있습니다.
SELECT
driver_id,
reason II ' - ' II COALESCE(offense_level, 'No Band') AS reason_and_severity,
fine_value + COALESCE(unpaid_fines, 0) AS total_due
FROM fines;
| driver_id | reason_and_severity | total_due |
| 16877165 | Speeding – Band B | 150.00 |
| 18314338 | Red Light – Band C | 5905.14 |
| 26177418 | Speeding – Band B | 350.00 |
| 41681615 | Stop Sign – No Band | 100.00 |
| 41681615 | Stop Sign – Band A | 100.00 |
글을 마무리하며
모든 관계형 데이터베이스 서버가 COALESCE 함수를 지원하는 것은 아닙니다. 하지만 이와 비슷한 기능을 수행하는 다른 함수들을 제공합니다. IFNULL 함수, NVL 함수, ISNULL 함수 등이 NULL 값을 발견하고, 대체하고, 변환시키는 기능을 수행할 수 있습니다.