[SQL] NULL 관련 함수 (NVL, ISNULL, NULLIF, COALESCE)

NULL 의 특성

• 아직 정의되지 않은 값으로 0 또는 공백과 다르다. (0은 숫자이고, 공백은 문자다)
• 테이블을 생성할 때 NOT NULL 또는 PRIMARY KEY로 정의되지 않으면 NULL을 포함할 수 있다. 
• NULL을 포함하는 연산의 결과도 NULL이다 ( 1 + NULL = NULL) 
  • 컬럼 A가 1 이고 컬럼 B가 NULL 일때는 연산 결과가 NULL 이지만, 
  • 컬럼 A에서 숫자 1과 NULL 이 있는 상태에서 sum(A)를 하면 NULL 을 제외한 1값이 나온다. 
• NULL과 연산하고 싶을 때에는 시스템에서 의미 없는 문자로 바꿔서 연산하는 경우가 많다. 혹은 NVL 함수를 이용할 수도 있다. 

 

NULL과 관련된 함수

NVL(표현식1, 표현식2) / ISNULL(표현식1, 표현식2)

표현식 1의 결과값이 NULL이면 표현식2의 값을 출력한다. 

단, 표현식1과 표현식2의 결과 데이터 타입이 같아야 한다. NULL관련해서 가장 많이 쓰이는 함수.

-- 오라클: NVL (NULL 판단 대상, 'NULL일 때 대체값')

SELECT NVL(NULL, 'NVL-OK') NVL_TEST
FROM   DUAL ;
--결과값 : NVL-OK


SELECT NVL('NOT-NULL', 'NVL-OK') NVL_TEST
FROM   DUAL ;
--결과값 : NOT-NULL (NULL이 아니니까 표현식 2가 아닌 1을 표기한 것임)

-- sql server : ISNULL (NULL 판단 대상, 'NULL일 때 대체값')

SELECT ISNULL(NULL, 'NVL-OK') ISNULL_TEST
-- 결과값: NVL-OK

SELECT ISNULL('NOT-NULL', 'NVL-OK') ISNULL_TEST
-- 결과값: NOT-NULL

 

선수 테이블에서 포지션이 없는 경우 '없음' 으로 표시하는 예시

-- 오라클 (NVL)
SELECT PLAYER_NAME 선수명, NVL(POSITION, '없음') 포지션
FROM   PLAYER ;

-- sql server(ISNULL)
SELECT PLAYER_NAME 선수명, ISNULL(POSITION, '없음') 포지션
FROM   PLAYER ;

NVL과 ISNULL은 CASE로 표현하는 것도 가능하다. 

SELECT PLAYER_NAME 선수명, 
       CASE WHEN POSITION IS NULL 
            THEN '없음'
            ELSE POSITION
       END AS 포지션
FROM   PLAYER ;

급여와 커미션을 포함한 연봉을 계산해보는 예시

SELECT ENAME 사원명, 
       (SAL * 12) + NVL(COMM, 0) 연봉  -- (급여 * 12개월) + (연봉 컬럼이 NULL 이면 0으로 계산)
FROM   EMP; 

 

NULLIF(표현식1, 표현식2)

표현식1이 표현식2와 같으면 NULL을, 같지 않으면 표현식1을 리턴한다.

특정 값을 NULL로 변경해야 할 때 유용하게 사용할 수 있다. 

-- NULLIF (표현식1, 표현식2) : 표현식1 과 2과 같으면 NULL, 다르면 표현식 1 리턴

-- MGR 7698 이면 NULL로 표시한다. 
SELECT ENAME, EMPNO, MGR, NULLIF(MGR, 7698) NUIF
FORM   EMP ; 

 

 

COALESCE(표현식1, 표현식2, ...)

임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다.

모든 표현식이 NULL이라면 NULL을 리턴한다.

 

사원 테이블에서 커미션, 급여를 표기하되, 두 컬럼 모두 NULL 이면 NULL을 표기하는 예시

-- COALESCE (표현식1, 표현식2)

SELECT ENAME, COALESCE(COMM, SAL) COAL
FROM   EMP ; 

 

 

공집합

공집합이란 SELECT 1 FROM DUAL WHERE 1=2 ; 와 같이 조건에 맞는 데이터가 한 건도 없는 경우를 의미한다. NULL과는 다르게 이해해야 한다. 

 

 

 

 

 

kdata 한국데이터진흥원에서 출간한 SQL 전문가 가이드 2013 Edition을 요약했습니다.