[MySQL] COALESCE 사용법, 예제, 활용하기 (NULL 치환)

COALESCE 함수

COALESCE 함수는 SQL에서 주어진 여러 표현식 중 첫 번째로 NULL이 아닌 값을 반환하는 함수입니다.
여러 값 중에서 NULL이 아닌 값을 선택할 때 유용하며, 모든 표현식이 NULL일 경우 NULL을 반환합니다.

 

 

사용방법

COALESCE(expression1, expression2, ..., expressionN);

 

-expression1, expression2, ..., expressionN 은 평가할 표현식들입니다.

-위 표현식들은 컬럼 이름일 수도 있고, 다른 함수의 결과일 수도 있으며, 고정된 값일 수도 있습니다.

-COALESCE 함수는 가장 첫 번째로 NULL이 아닌 값을 반환합니다.

-만약 모든 표현식이 NULL이라면, COALESCE 함수는 NULL을 반환합니다.

 

테이블 생성 및 데이터 삽입

CREATE TABLE scheduler.tbl_employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    middle_name VARCHAR(50),
    last_name VARCHAR(50),
    phone_number VARCHAR(20),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    event_date DATE
);

INSERT INTO scheduler.tbl_employees (id, first_name, middle_name, last_name, phone_number, department, salary, event_date) VALUES
(1, '홍', NULL, '동', NULL, 'Sales', 5000, '2024-01-15'),
(2, '이', '순', '신', '010-1234-1234', 'HR', 6000, NULL),
(3, NULL, NULL, '대왕', NULL, 'IT', NULL, '2024-02-20'),
(4, '강', '감', '찬', NULL, NULL, 4500, NULL),
(5, NULL, NULL, NULL, NULL, 'Finance', 5500, '2024-03-10');

 

테이블 조회

select * from scheduler.tbl_employees te;

 

 

예제 1: 조건에 따라 동적 컬럼 선택

SELECT id,
       COALESCE(first_name, middle_name, last_name) AS full_name
FROM scheduler.tbl_employees;

 

-first_name, middle_name, last_name 중 NULL이 아닌 첫 번째 값을 full_name으로 반환합니다.

 

 

예제 2: NULL 값을 대체하여 출력

SELECT id,
       COALESCE(phone_number, 'No Phone Number') AS contact_number
FROM scheduler.tbl_employees;

 

-phone_number 컬럼이 NULL인 경우 'No Phone Number'를 반환합니다.

 

예제 3: 집계 함수와 함께 사용

SELECT department,
       COALESCE(SUM(salary), 0) AS total_salary
FROM scheduler.tbl_employees
GROUP BY department;

 

-부서별로 급여의 합계를 계산하는데, 급여의 합계가 NULL인 경우 0을 반환합니다.

 

 

예제 4: 데이터 통합

SELECT id, first_name, last_name,
       COALESCE(department, 'No Department') AS department_name
FROM scheduler.tbl_employees;

 

-tbl_employees 테이블에서 department 컬럼이 NULL인 경우 'No Department'를 반환하여 department_name으로 출력합니다.

 

 

예제 5: 날짜 값 처리

SELECT id,
       COALESCE(event_date, '2024-01-01') AS event_date
FROM scheduler.tbl_employees;

 

-event_date가 NULL인 경우 '2024-01-01'을 반환합니다.

 

예제 6: 데이터 정규화

SELECT id,
       COALESCE(department, 'Unknown Department') AS department_name
FROM scheduler.tbl_employees;

 

-department가 NULL인 경우 'Unknown Department'를 반환합니다.

 

 

예제 7: 다중 조건 대체

SELECT id,
       COALESCE(NULLIF(first_name, ''), NULLIF(last_name, ''), 'Anonymous') AS display_name
FROM scheduler.tbl_employees;

-first_name이 빈 문자열이면 last_name을 사용하고, last_name도 빈 문자열이면 'Anonymous'를 반환합니다.