공부하는 습관

SQLD 시험 전 공부해야 할 것3 : NVL, NULLIF, COALESCE

SQL에서 NULL값을 처리하고 싶은데 어떻게 하면 좋을까요?

이때 활용하는 함수인 NVL, NULLIF, COALESCE를 알아보도록 해요!

NULLIF

두 식을 비교하여 표현식2와 동일하면 null을 반환하고 동일하지 않으면 표현식1을 반환합니다. 구문은 NULLIF(expression1, expression2)입니다. 이 함수는 종종 오류를 방지하거나 null 값을 기본값으로 대체하는 데 사용됩니다.

SELECT NULLIF(5,5); -- null을 반환합니다
SELECT NULLIF(5,6); -- 5를 반환합니다

예시문제

1.제품 목록과 가격을 반환하지만 무료가 아닌 제품만 포함하는 쿼리를 작성합니다.

SELECT product_name, price
FROM products
WHERE NULLIF(price, 0) IS NOT NULL;

2. 각 제품의 평균 등급을 계산하지만 0인 등급은 제외하는 쿼리를 작성합니다

SELECT product_id, AVG(NULLIF(rating, 0)) AS avg_rating
FROM ratings
GROUP BY product_id;

COALESCE

이 함수는 식 목록에서 null이 아닌 첫 번째 값을 반환합니다. 구문은 COALESCE(식 1, 식 2, ..., 식 N)입니다. 이 함수는 종종 쿼리에서 null 값을 처리하거나 null 값을 기본값으로 대체하는 데 사용됩니다.

SELECT COALESCE(NULL, 1, 2); -- 1을 반환합니다
SELECT COALESCE(NULL, NULL, 'hello', 'world'); -- 'hello'를 반환합니다

예시문제

1. 고객 이름 및 연락처 전자 메일 목록을 반환하는 쿼리를 작성합니다. 고객에게 연락처 전자 메일이 없는 경우 기본 전자 메일을 대신 반환합니다.

SELECT name, COALESCE(contact_email, primary_email) AS email
FROM customers;

NVL

이 기능은 COALESCE와 유사하지만 Oracle 데이터베이스에만 해당됩니다. 식 목록에서 null이 아닌 첫 번째 값을 반환합니다. 구문은 NVL(expression1, expression2)입니다. 이 함수는 종종 쿼리에서 null 값을 처리하거나 null 값을 기본값으로 대체하는 데 사용됩니다.

SELECT NVL(NULL, 1); -- 1을 반환합니다
SELECT NVL(NULL, 'hello'); -- 'hello'를 반환합니다

예시문제

1.직원 및 부서 이름 목록을 반환하는 쿼리를 작성합니다. 직원이 부서에 할당되지 않은 경우, 대신 "할당되지 않음"을 반환합니다.

SELECT employee_name, NVL(department_name, '할당되지 않음') AS department
FROM employees;

2. 각 제품의 총 매출을 계산하는 쿼리를 작성하고, 매출이 없는 제품의 경우 매출에 대해 0을 반환합니다.

SELECT product_id, NVL(SUM(sales_amount), 0) AS total_sales
FROM sales
GROUP BY product_id;

NVL2

SQL에서 식을 null이 아닌 경우 반환할 값과 식이 null인 경우 반환할 다른 값을 지정할 수 있는 함수입니다.

NVL2 (expression, value_if_not_null, value_if_null)

expression: null을 확인할 식입니다
value_if_not_null: 식을 null이 아닌 경우 반환할 값
value_if_null: 식이 null인 경우 반환할 값

SELECT NVL2(salary, 'has salary', 'no salary') AS salary_status
FROM employees;

예시문제

1. 직원 및 직함 목록을 반환하는 조회를 작성합니다. 직원에게 직함이 할당되지 않은 경우, "No title assigned"를 대신 반환합니다.

SELECT employee_name, NVL2(job_title, job_title, 'No title assigned') AS job_title
FROM employees;

2. 각 영업 사원의 수수료를 계산하는 쿼리를 작성합니다. 판매원이 판매를 하지 않은 경우 "수수료 없음"을 대신 반환합니다.

SELECT salesperson_name, NVL2(total_sales, total_sales * 0.1, 'No commission') AS commission
FROM salespeople;