오라클 NULL 치환 함수 정리 (NVL, NVL2, COALESCE, NULLIF)

Oracle NULL 치환 함수 정리 (NVL, NVL2, COALESCE, NULLIF)

해당 포스팅은 oracle null 치환 함수에 대해서 정리한 내용입니다.

null 치환 함수는 실무에서도 쓰이면서 여러 시험 문제로도 많이 출제되기 때문에 잘 정리해 두면 좋을 것 같습니다.

 

---

NVL, NVL2

-- NVL 함수 형식
NVL(주어진 값, 대체 값)

 

먼저 NVL 함수의 경우 '주어진 값'이 NULL인 경우 '대체 값'을 반환하며, 주로 NULL 값을 다른 값으로 대체할 때 사용됩니다.

해당 함수는 '주어진 값'과 '대체 값'의 데이터 타입이 동일해야 하며, 동일하지 않을 경우 오류가 발생하게 됩니다.

 

NVL 사용 예시

간단한 사용 예시로는 다음과 같이 시험을 치지 않아 시험 점수(SCORE)가 NULL인 데이터에 대해 NULL을 0으로 치환하여 출력할 수 있습니다.

 

 

-- NVL2 함수 형식
NVL2(주어진 값, 대체 값1, 대체 값2)

 

이어서 NVL2 함수의 경우 '주어진 값'이 NULL인지 아닌지에 따라 두 개의 다른 값을 반환하는데요.

'주어진 값'이 NULL이 아닐 때 '대체 값1'을 반환하며, NULL인 경우 '대체 값2'를 반환합니다.

 

해당 함수 역시 '대체 값1'과 '대체 값2'의 데이터 타입이 동일해야 하며, 동일하지 않을 경우 오류가 발생합니다.

 

NVL2 사용 예시

간단한 예시로는 시험에 응시하지 않아 시험 점수(SCORE)가 NULL인 데이터에 대해 '미응시'를 반환하고, 시험에 응시하여 시험 점수가 NULL이 아닌 데이터에 대해 '응시'를 반환하도록 사용할 수 있습니다.

 

 

* NVL, NVL2 함수의 경우 오라클에서만 사용 가능한 함수입니다.

 

 

---

COALESCE

-- COALESCE 함수 형식
COALESCE(주어진 값1, 주어진 값2, ... 주어진 값n)

 

함수의 인자들 중에서 NULL이 아닌 첫 번째 값을 반환합니다.

최소 두 개 이상의 인자를 받아야 하며, 모든 인자의 데이터 타입은 동일해야 합니다.

만약 함수의 모든 인자가 NULL인 경우 함수의 결과로 NULL을 반환합니다.

 

COALESCE 함수의 경우 복수의 컬럼이나 표현식에 대해 NULL이 아닌 첫 번째 값을 반환할 수 있기 때문에 여러 조건에 따른 대체 값을 처리할 때 특히 유용합니다.

 

* COALESCE 함수의 경우 SQL 표준이기 때문에 오라클 외 다른 DBMS에서도 사용할 수 있습니다.

 

COALESCE 사용 예시

간단한 사용 예시로는 다음과 같이 전화번호(phone_number), 휴대전화번호(mobile_number), 회사 전화번호(office_phone_number) 컬럼이 있을 때, 데이터의 값이 NULL이 아닌 첫 번째 값을 반환하도록 할 수 있습니다.

 

 

---

NULLIF

-- NULLIF 함수 형식
NULLIF(비교할 값1, 비교할 값2)

 

NULLIF 함수의 경우 NULL 치환 함수는 아니지만 NULL 처리와 관련된 함수이기 때문에 함께 내용에 추가하였습니다.

 

NULLIF 함수의 경우 '비교할 값1'과 '비교할 값2'를 비교하여 두 값이 같을 경우 NULL을 반환하고 다를 경우 '비교할 값1'을 반환하는 함수입니다.

해당 함수 역시 인자로 받는 값의 데이터 타입이 동일해야 오류가 발생하지 않습니다.

 

* NULLIF 함수는 오라클에서만 사용 가능하며 MySQL 등에서는 NULLIF 대신 CASE 표현식을 활용할 수 있습니다.

 

NULLIF 사용 예시1

NULLIF 사용 예시2

간단한 예시로는 다음과 같이 함수에 주어진 두 개의 인자가 동일할 때 NULL을 반환하고, 동일하지 않을 경우 첫 번째 인자 값을 반환하는 것을 볼 수 있습니다.