[SQL] 오라클(Oracle) NULL 관련 함수 정리

NVL(A, B)

입력 값 A가 NULL 일 경우 B로 대체하여 반환한다.

SELECT NVL(NULL, '홍길동') AS NAME,
    NVL(NULL, 20) AS AGE,
    NVL(NULL, SYSDATE) AS CREATE_AT
FROM dual;

NAME	AGE	MODIFIED_DATE
홍길동	20	2023-12-31 23:59:59

 

SELECT NVL('오라클', '홍길동') AS NAME,
    NVL(27, 20) AS AGE,
    NVL(TO_DATE('2023-01-01 00:00:00'), SYSDATE) AS CREATE_AT
FROM dual;

NAME	AGE	MODIFIED_DATE
오라클	27	2023-01-01 00:00:00

 

 

NVL2(A, B, C)

입력 값 A가 NULL이 아니면 B, NULL이면 C로 변환하여 반환한다.

SELECT NVL2(NULL, '있음', '없음') AS CASE1,
    NVL2('홍길동', '있음', '없음') AS CASE2,
    NVL2(NULL, 1, 0) AS CASE3,
    NVL2(20, 1, 0) AS CASE4
FROM dual;

CASE1	CASE2	CASE3	CASE4
없음	있음	0	1

 

 

COALESCE(A, B, C, …)

A가 널이면 B, B도 NULL이면 C와 같이 변환하여 반환한다.

• 다시 말해 NULL이 아닌 첫 번째 인자의 값을 반환하며, 모든 인자가 NULL이면 NULL을 반환한다.

COALESCE 함수는 NVL 함수보다 확장된 개념으로 여러 개의 컬럼의 NULL 값 여부를 판별하면서 값을 변환할 때 사용한다.

• NVL 함수와 동일한 기능으로 사용할 수도 있다.

SELECT COALESCE('A', 'B', 'C', 'D') AS CASE1,
    COALESCE(NULL, 'B', 'C', 'D') AS CASE2,
    COALESCE(NULL, NULL, 'C', 'D') AS CASE3,
    COALESCE(NULL, NULL, NULL, 'D') AS CASE4,
    COALESCE(NULL, NULL, NULL, NULL) AS CASE5
FROM dual;

CASE1	CASE2	CASE3	CASE4	CASE5
A	B	C	D

 

 

NULLIF(A, B)

입력 값 A가 B와 같으면 NULL로 변환하여 반환한다.

SELECT NULLIF('Hello', 'Hello') AS CASE1,
    NULLIF('Oracle' 'Hello') AS CASE2,
    NULLIF(1234, 1234) AS CASE3,
    NULLIF(1234, 5678) AS CASE4,
    NULLIF(SYSDATE, SYSDATE) AS CASE5,
    NULLIF(TO_DATE('2023-01-01', 'YYYY-MM-DD'), SYSDATE) AS CASE6
FROM dual;

CASE1	CASE2	CASE3	CASE4	CASE5	CASE6
	Oracle		1234		2023-01-01 00:00:00

 

 

LNNVL(조건식)

조건식에서 한쪽 또는 양쪽 피연산자에 NULL이 존재할 경우 조건문을 평가하여 결과를 반환한다.

• LNNVL 함수는 WHERE 절에 사용된다.
• 조건문 내에 존재하는 컬럼의 값이 NULL 이면 TRUE를 반환한다.
• 조건문에 해당하는 값이 FALSE나 UNKNOWN이면 TRUE를 반환한다.
• 조건문이 TRUE이면 FALSE를 반환한다.

다음 표는 a = 2이고, b is null인 경우, LNNVL이 반환하는 값을 정리한 표이다.

조건식	조건식 결과	LNNVL 반환
a = 1	FALSE	TRUE
a = 2	TRUE	FALSE
a IS NULL	FALSE	TRUE
b = 1	UNKNOWN	TRUE
b IS NULL	TRUE	FALSE
a = b	UNKNOWN	TRUE

 

사용 예제

다음과 같이 EXAMPLE 테이블이 있다고 가정한다.

ID	AGE
1
2	17
3	25
4	32
5	58

SELECT ID, AGE
FROM EXAMPLE
WHERE LNNVL(AGE < 50);

ID	AGE
1
5	58

• 조건문 내에 존재하는 컬럼의 값이 NULL 이면 TRUE를 반환한다.
  • ID가 1인 AGE는 NULL 이므로 WHERE 절이 TRUE가 되어 조회된다.
• 조건문에 해당하는 값이 FALSE나 UNKNOWN이면 TRUE를 반환한다.
  • ID가 5인 AGE는 58이므로 AGE < 50 조건에 FALSE이므로 TRUE를 반환해 조회된다.
• 조건문이 TRUE이면 FALSE를 반환한다.
  • ID가 2, 3, 4인 경우 AGE < 50 조건에 TRUE이므로 FALSE를 반환해 조회되지 않는다.

 

아래 코드는 위 코드와 같은 결과를 출력한다.

SELECT ID, AGE
FROM EXAMPLE
WHERE AGE IS NULL OR AGE > 50;