데이터베이스 첫걸음 정리 - 15장. CASE 문으로 데이터 변환하기

함수를 사용하면 데이터를 임의의 형태로 변경할 수 있지만, 특정 상황에 따라 데이터를 변경하고 싶을 수도 있을 수 있습니다.

이런 경우 CASE문을 활용할 수 있습니다.

CASE 문

특정한 상황에 특정 데이터를 반환시키고 싶을때 CASE문을 활용하면 간단하게 해결할 수 있습니다.

CASE문은 다음과 같은 형태로 사용합니다.

CASE WHEN 조건식 THEN 식1
	[WHEN 조건식2 THEN 식2]
	[ELSE 식3]
END

먼저 WHEN 절에는 참과 거짓을 반환하는 조건식을 적습니다.

해당 조건을 만족하여 참이 되는 경우는 THEN 절에 기술한 식이 처리됩니다.

WHEN 절의 조건식을 차례로 비교해 나가다가 가장 먼저 조건을 만족한 WHEN 절과 대응하는 THEN 절 식의 처리결과를 CASE 문의 결과값으로 반환합니다.

만약 어떤 조건도 만족하지 못한 경우의 동작을 정의하고 싶은 경우 ELSE를 사용하면 됩니다.

ELSE는 생략 가능하며, 생략했을 경우 ELSE NULL로 간주됩니다.

이제 CASE문을 응용하여 NULL 값을 특정 값으로 변환하는 CASE 식 구현사례를 살펴봅시다.

(현재 테이블 안의 데이터)

SELECT 
CASE WHEN email IS NULL THEN "not found" 
ELSE email 
END 
FROM mysql_test_a;

실행 결과

email 열의 값이 NULL이면 참이 되므로 “not found”를 출력하는 것을 확인할 수 있습니다.

NULL이 아니라면 email을 그대로 출력하는 것을 확인할 수 있습니다.

COALESCE

사실 NULL 값을 반환할때에는 COALESCE 함수를 사용하는 것이 더 쉬울 수 있습니다.

앞선 CASE WHEN 구문을 COALESCE 함수를 사용하면 다음과 같이 표현할 수 있습니다.

SELECT COALESCE(email, "not found") FROM mysql_test_a;

실행해보면 위와 같이 결과가 잘 나오는 것을 볼 수 있습니다.

COALESCE 함수는 NULL이 아닐 경우 첫번째 파라미터를 출력하고 그렇지 않으면 두번째 파라미터 값을 출력하게 됩니다.

또 하나의 CASE 문

숫자로 이루어진 코드를 알아보기 더 쉽게 문자열로 변환하고 싶은 경우 CASE 문을 사용하면 쉽게 변환할 수 있습니다.

예를 들어, 1은 남자, 2는 여자라는 코드 체계가 있다면 이를 단순히 숫자로 출력하는 것보다는 남자/여자로 출력하는 것이 알아보기 쉬울 것입니다.

이와 같이 문자화시키는 것을 디코드라고 부르고 반대로 수치화 시키는 것은 인코드라고 부릅니다.

이와 같은 디코드를 CASE문으로 쉽게 처리할 수 있습니다.

WHEN gender = 1 THEN '남자'
WHEN gender = 2 THEN '여자'

CASE문은 “검색 CASE”와 “단순 CASE”의 두 개 구문으로 나눌 수 있습니다.

검색 CASE는 앞서 설명한 구문으로 다음과 같습니다.

CASE WHEN 조건식 THEN 식 ...

단순 CASE는 다음과 같이 사용합니다.

CASE 식1 
WHEN 식2 THEN 식3
WHEN 식4 THEN 식5
ELSE 식6

단순 CASE는 CASE 뒤에 식을 기술하고 WHEN 뒤에 (조건식이 아닌) 식을 기술합니다.

식1의 값이 WHEN의 식2의 값과 동일한지 비교하고 값이 간다면 식3의 값이 CASE 문 전체의 결과값이 됩니다.

값이 같지 않다면 다음 WHEN 절과 비교하는 식으로 진행되게 됩니다.

이번에는 숫자로 이루어진 성별을 디코딩하는 예제를 살펴봅시다.

먼저, 검색 CASE의 경우를 봅시다.

SELECT firstname, 
CASE  
WHEN gender=1 THEN 'male' 
WHEN gender=2 THEN 'female' 
ELSE 'undefined' 
END AS 'gender' 
FROM mysql_test_b;

검색 CASE 경우 gender=1 gender=2 처럼 상세하게 조건을 작성하고 있습니다.

하지만 단순 CASE의 경우에는 비교할 항목을 따로 지정하므로 WHEN에는 비교할 값만 적어도 되서 좀 더 깔끔하게 작성할 수 있습니다

이제 단순 CASE의 경우를 살펴봅시다.

SELECT firstname, 
CASE gender
WHEN 1 THEN 'male' 
WHEN 2 THEN 'female' 
ELSE 'undefined' 
END AS 'gender' 
FROM mysql_test_b;

CASE를 사용할 경우 주의사항

이제 까지 예제에서는 SELECT 구에서 CASE 문을 사용했습니다.

그러나 CASE 문은 어디에나 사용할 수 있습니다. WHERE 구에서 조건식의 일부로 사용될 수도 있고 ORDER BY 구나 SELECT 구에서도 사용할 수 있습니다.

• ELSE 생략

ELSE를 생략하면 ELSE NULL이 되는 것에 주의해야 합니다.

생각한 것 이외의 데이터가 들어오는 경우에 대응하는 WHEN이 하나도 없으면 ELSE 절이 사용됩니다.

이때 ELSE를 생략하면 생각하지 못한 데이터가 왔을 때 NULL이 반환됩니다.

따라서 ELSE는 생략하지 않고 지정하는 편이 낫습니다.

• WHEN에 NULL 지정하기

단순 CASE에서는 WHEN 뒤에 1개의 상수값을 지정하는 경우가 많습니다.

앞에서 살펴본 예제 처럼 WHEN 1 THEN ‘male’ WHEN 2 THEN ‘여자'와 같이 쓸 수 있습니다.

여기서 데이터가 NULL 인 경우를 고려해서 WHEN NULL THEN ‘데이터 없음'과 같이 지정해도 문법적으로는 문제가 없지만 정상적으로 처리되지 않습니다.

그 이유에대해서 알아봅시다.

단순 CASE 문에서는 CASE에서 지정된 식과 WHEN에서 지정된 식을 비교해가며 확인합니다.

CASE gender
	WHEN 1 THEN '남자'
	WHEN 2 THEN '여자'
	WHEN NULL THEN '데이터 없음'
	ELSE '미지정'
END

위의 예제에서는 다음과 같은 순서로 조건식이 처리되게 됩니다.

1. gender = 1
2. gender = 2
3. gender = NULL

비교 연산자 = 로는 NULL 값과 같은지 아닌지를 비교할 수 없습니다.

따라서, gender의 값이 NULL이라 해도 gender = NULL이 참이 되지 않습니다.

NULL 값인지 확인하기 위해서는 IS NULL 연산자를 사용해야 하는데 단순 CASE 문 특성상 = 연산자로만 비교하므로 NULL인지 판단하기 위해서는 검색 CASE 문을 사용해야 합니다.

CASE
	WHEN gender = 1 THEN '남자'
	WHEN gender = 2 THEN '여자'
	WHEN gender IS NULL THEN '데이터 없음'
	ELSE '미지정'
END

• DECODE NVL

Oracle에는 이 같은 디코드를 수행하는 DECODE 함수가 내장되어 있습니다.

DECODE 함수는 CASE 문과 같은 용도로 사용할 수 있습니다.

다만 DECODE 함수는 Oracle에서만 지원하는 함수인 만큼 다른 데이터베이스 제품에서는 사용할 수 없습니다.

그에 비해 CASE 문은 표준 SQL로 규정된 덕분에 많은 데이터베이스 제품이 지원합니다.

또한 NULL 값을 변환하는 함수도 있는데 Oracle에서는 NVL 함수, SQL Server에서는 IS NULL 함수가 이에 해당합니다.

다만 이 함수들은 특정 데이터베이스에 국한된 함수인 만큼 NULL 값을 변환할 때는 표준 SQL로 규정되어 있는 COALESCE 함수를 사용합니다.

정리

• CASE 문을 사용하면 특정 상황에 따라서 데이터를 변경할 수 있습니다.
• NULL 값을 특정 값으로 대체하고 싶을 경우 COALESCE 함수를 사용하면 간단하게 해결할 수 있습니다.
• CASE문은 검색 CASE와 단순 CASE로 나눌 수 있습니다.
• 검색 CASE는 WHEN 뒤에 조건식을 기술합니다.
• 단순 CASE는 CASE 뒤에 식을 기술하고 WHEN 뒤에 (조건식이 아닌) 식을 기술합니다.
• CASE에서 ELSE를 생략하는 경우 ELSE NULL로 처리됩니다.
• 단순 CASE에서 WHEN은 비교연산자 = 역할을 하기 때문에(NULL 비교는 IS NULL을 사용해야함) NULL을 판단해야 하는 경우 검색 CASE를 사용해야 합니다.