[DB/ORACLE/MSSQL(SQLSERVER)] NULL의 이해 (NVL,NVL2,NULLIF,COALESCE)

데이터베이스에서 NULL 값은 중요합니다. NULL 값 특징에 대해서 이해하지 못하면 SQL 쿼리 수행결과가 예상한 것과 다르게 나올 수 있습니다. NULL 값은 다른 DBMS와 성질이 똑같지만 ORACLE / MSSQL(SQLSERVER) DBMS에 대해서 포커스를 두고 설명하겠습니다. 

 

 

목차

 

---

NULL 값 특징

NULL 값은 비어있는 값이 아닙니다. 모르는 갓이며 값의 부재를 의미합니다. 숫자 0과 다르며 문자 공백과는 다른 UNKONW으로써 알 수 없거나 존재하지 않는 값을 말합니다.

 

---

NULL 값 연산과 처리

NULL 값과 다른 값들을 가지고 연산을 하게 되면 NULL을 출력하게 됩니다. 

SELECT NULL + 1;
SELECT NULL + '문자열';
SELECT NULL * 1;
SELECT NULL / 1;

위 연산의 결과는 모두 NULL입니다. 수많은 값들을 나열하여 계산한다 하더라도 NULL 값이 오게 되면 결과는 항상 같습니다. NULL 값에 대해서 비교 연산을 사용하게 되면 FALSE를 리턴합니다. 따라서 정확한 연산을 위해서는 NULL 값을 잘 처리해주어야 합니다. NULL 값을 비교하기 위해서는 IS NULL / IS NOT NULL을 사용합니다. 사용 방법은 다음과 같습니다.

//email 칼럼에 NULL 값이 있는 경우를 제외하고 USERS 테이블에서 name을 출력 
SELECT name FROM USERS WHERE email IS NOT NULL

//email 칼럼이 NULL 인 경우 USERS 테이블에서 name을 출력
SELECT name FROM USERS WHERE email IS NULL

만약 위의 SQL에서 WHERE 절이 email = ''  또는 email != ''을 하게 되면 같은 결과가 출력될까요? NULL 값과 빈 문자열은 다릅니다. 아예 없는 것과 비어있는 것은 달라서 결과가 달라질 수 있습니다. email 값이 빈 문자열로 되어있는 경우가 있으면 아래와 같이 WHERE절을 한다면 해당 칼럼이 조회가 되며 NULL 값으로 되어있다면 조회가 되지 않습니다.

//email 칼럼에 빈 문자열 값이 있는 경우 USERS 테이블에서 name을 출력 
SELECT name FROM USERS WHERE email = ''

//email 칼럼에 빈 문자열 값이 아닌 경우 USERS 테이블에서 name을 출력 
SELECT name FROM USERS WHERE email != ''

---

 

NULL 관련함수

NULL을 처리하기 위해 다양한 함수들이 존재합니다. 관련함수에 대해서 예시 SQL문을 통해서 알아보겠습니다. 다음은 예시를 위해 USERS테이블을 만들었습니다. 3명의 데이터가 존재하며 Email 값이 NULL 값이 들어간 경우 빈 문자열이 들어간 경우 그리고 정상적으로 입력된 경우가 있습니다.

예시 테이블
USERS
userId	userName	userEmail
1	심길동	NULL
2	김공차
3	강치환	java@oracle.com

 

해당 컬럼명의 값이 NULL인 경우 지정값으로 리턴하는 함수

ORACLE - NVL(칼럼명, 지정값) 

SQL Server - ISNULL컬럼명, 지정값) 

아래 예시 SQL문과 실행결과를 확인해 보면 NULL인 경우 'UNKNOWN'으로 치환이 되었고 빈칸에 대해서는 치환되지 않은 것을 확인할 수 있습니다.

//ORACLE
SELECT nvl(email,'UNKOWN') AS eamil FROM USERS

//SQL Server
SELECT ISNULL(email,'UNKOWN') AS eamil FROM USERS

email

UNKOWN

java@oracle.com

 

해당 컬럼명의 값이 NULL이 아닐 경우 지정값 1, NULL일 경우 지정값 2로 리턴하는 함수

ORACLE - NVL2(컬럼명, 지정값 1, 지정값 2) 

아래 SQL 실행 결과를 통해 NULL 값인 경우 지정값2인 'unknown'을 출력하게 되고 나머지 값에 대해서는 'NOT unknown' 값을 출력한 것을 확인할 수 있습니다.

SELECT nvl2(email,'NOT UNKOWN'.'UNKOWN') AS eamil FROM USERS

email

UNKOWN

NOT UNKOWN

NOT UNKOWN

해당 지정값1의 값이 지정값 2와 같을 경우 NULL, 같지 않으면 지정값 1을 리턴하는 함수
ORACLE - NULLIF(지정값1,지정값2)

지정값에는 칼럼명이 들어가도 됩니다. 아래 예시를 보면 이름이 '심길동'인 경우 NULL 값을 리턴하고 같지 않다면 userName칼럼의 값을 출력하는 SQL입니다.

SELECT NULLIF(userName,'심길동') AS 이름 FROM USERS

name

NULL

김공차

강치환

 

임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 리턴하는 함수 

SQLSERVER / ORACLE - COALESCE ( 표현식 1, 표현식 2,.... ) 

모든 표현식이 NULL일 경우 NULL을 리턴한다. 이 함수의 예시는 2개로 나눠서 확인합니다. NULL 값이 있는 유저인 심길동 데이터만 표기하기 위해 WHERE 절을 사용하였습니다. 결과 1을 보면 email, userName, userId 순으로 인자를 주었을 경우 userName이 최초의 NULL값이 아니기 때문 userName이 출력되며 결과 2를 보면 userId, userName, eamil 순으로 인자를 주었을 경우 userId가 출력되는 것을 확인할 수 있습니다.

SELECT COALESCE(eamil,userName,userId) AS 결과1 FROM USERS WHERE userId = 1

SELECT COALESCE(userId,userName,eamil) AS 결과2 FROM USERS WHERE userId = 1

결과1	결과2
심길동	1