[DATABASE] CASE / DECODE / 조건 선택문

1. CASE문 

- 하나의 컬럼으로 조건값을 비교하여 출력값을 결정

SELECT first_name, department_id,
CASE department_id     --조건값을 비교할 컬럼
    WHEN 10 THEN 'TEAM - 1' --WHEN 조건값 THEN 출력값
    WHEN 20 THEN 'TEAM - 2'
    WHEN 30 THEN 'TEAM - 3'
    ELSE 'OTHERS'
END "TEAM"
FROM employees
WHERE department_id < 50;

- 여러 컬럼을 사용해서 조건식을 작성할 경우 아래와 같이 작성 가능

SELECT first_name, department_id,
CASE 
    WHEN department_id BETWEEN 10 AND 50 THEN 'TEAM - 1'
    WHEN department_id BETWEEN 60 AND 100 THEN 'TEAM - 2'
    WHEN department_id BETWEEN 110 AND 150 THEN 'TEAM - 3'
    ELSE 'OTHERS'
END "TEAM"
FROM employees;

SELECT first_name, department_id,
CASE 
    WHEN first_name = 'Steven' THEN 'TEAM - 1'
    WHEN department_id = 30 THEN 'TEAM - 2'
    WHEN department_id BETWEEN 110 AND 150 THEN 'TEAM - 3'
    ELSE 'OTHERS'
END "TEAM"
FROM employees;

 

2. DECODE

DECODE(컬럼,조건,TRUE 결과값,FALSE 결과값)

SELECT first_name, department_id,
DECODE(department_id, 10, 'TEAM - 1',
                    20, 'TEAM - 2',
                    30, 'TEAM - 3',
                    'OTHERS') "TEAM"
FROM employees
WHERE department_id < 50;