[Oracle/MySQL] NVL과 IFNULL, COALESCE로 널 값 처리하기

널 값인 경우에 null을 그대로 출력할 수는 없으니

다른 값으로 출력하기 위한 함수가 존재하는데, 오늘은 그에 대해서 정리를 해보려 한다.

밑에 DEPARTMENT 테이블을 이용해 알아보자.

deptno	name	location
1	Finance	New York
2	Development	-

현재, deptno=2이고 name='Development'인 데이터의 location이 널값이다.

---

Oracle에서 널 값 처리하기

오라클에서는 NVL을 통해 널값을 처리할 수도 있고, 값이 있는 경우 원하는 값으로 대체하여 출력할 수도 있다.

 

NVL(column명, 'null일 때 출력값') 로  널 값 처리하기

SELECT name,location,NVL(location,'none') FROM departments;

- 실행 결과

deptno	name	location	NVL(location,'none')
1	Finance	New York	New York
2	Development	[NULL]	none

NVL에 첫번째 매개변수로 넣어준 칼럼 데이터가 널값이면 'none'을 출력하라는 명령이다.

( location을 같이 조회한 이유는 비교를 위해서이다.)

 

NVL2(column명,'null일 때 출력값','null이 아닐 때 출력값') 로 널 값 처리하기

SELECT deptno,name,location,NVL2(location,'Y','N') FROM DEPARTMENTS;

- 실행 결과

deptno	name	location	NVL2(location,'Y','N')
1	Finance	New York	Y
2	Development	[NULL]	N

NVL2에 첫 번째 매개변수는 column명, 두 번째 매개변수로는 null값이 아닐 때 출력하고 싶은 문자열, 세 번째 매개변수는 null값일 때 출력하고 싶은 문자열을 넣어 명령을 실행한다.

 

COALESCE( exp1, exp2, exp3, .. ) 으로 널 값 처리하기

INSERT INTO DEPARTMENTS(deptno, location) VALUES (3,'Seoul');
-- name이 null값인 데이터 추가
INSERT INTO DEPARTMENTS(deptno) VALUES (4);
-- name과 location 모두 null 값인 데이터 추가

SELECT deptno,name,location,COALESCE(location, name, 'none') FROM DEPARTMENTS;

- 실행 결과

deptno	name	location	COALESCE(location, name, 'none')
1	Finance	New York	New York
2	Development	[NULL]	Development
3	[NULL]	Seoul	Seoul
4	[NULL]	[NULL]	none

위의 예시를 통해 정리해보자면,

location 칼럼 데이터가 없으면 name 칼럼 데이터를 출력하고 name 칼럼 데이터가 없으면 none 문자열을 출력하는 명령이다. 

---

MySQL에서 널 값 처리하기

해당 칼럼이 널 값인 경우 IFNULL() 을 사용하며, 오라클과 마찬가지로 COALESCE() 함수를 제공한다.

 

IFNULL(column명, 'null일 때 출력값') 으로 널 값 처리하기

SELECT deptno,name,location,IFNULL(location,'none') FROM DEPARTMENTS;

- 실행 결과

deptno	name	location	IFNULL(location,'none')
1	Finance	New York	New York
2	Development	[NULL]	none

오라클의 NVL() 과 생긴 것만 다르고 사용법이 똑같다.

IFNULL의 첫번째 매개변수인 칼럼 데이터, location이 널 값이면 두 번째 매개변수에 해당하는 값이 출력된다.

 

CASE-WHEN절로 널 값 처리하기

SELECT deptno,name, CASE 
                      WHEN location IS NULL THEN 'N' 
                      ELSE 'Y' 
                    END AS location2 -- AS 별칭은 안해도 무방
FROM DEPARTMENTS;

- 실행 결과

deptno	name	location	location2
1	Finance	New York	Y
2	Development	[NULL]	N

오라클도 CASE-WHEN을 통해 위와 같이 처리할 수 있지만 NVL2() 함수를 지원하기 때문에 굳이 조건식을 만들 필요가 없다. 하지만 MySQL에서 널 값과 그렇지 않은 값에 대한 정의를 해야한다면 위와 같이 쓸 수 있다. 

CASE WHEN 조건 THEN 참일 때 출력할 값 ELSE 거짓일 때 출력할 값 END

 

COALESCE( exp1, exp2, exp3, .. ) 으로 널 값 처리하기

INSERT INTO DEPARTMENTS(deptno, location) VALUES (3,'Seoul');
-- name이 null값인 데이터 추가
INSERT INTO DEPARTMENTS(deptno) VALUES (4);
-- name과 location 모두 null 값인 데이터 추가

SELECT deptno,name,location,COALESCE(location, name, 'none') FROM DEPARTMENTS;

- 실행 결과

deptno	name	location	COALESCE(location, name, 'none')
1	Finance	New York	New York
2	Development	[NULL]	Development
3	[NULL]	Seoul	Seoul
4	[NULL]	[NULL]	none

오라클의 COALESCE() 함수와 같다. 

location이 널 값이면 name이 출력되고, name이 널 값이면 'none'이 출력된다. 

매개변수를 연달아 추가해도 동작이 같다.

 

---

정리

오라클에서는 널 값을 처리하기위해 NVL() 함수를 쓰고, MySQL에서는 IFNULL()을 쓴다.

공통적으로 COALESCE() 함수를 지원한다.

CASE-WHEN을 쓰는 것보다 지원하는 함수를 찾아쓰는 게 보기 깔끔한 것 같으니 알아두는 게 좋을 것 같다.