[Ordcle] CASE 구문

쿼리만 사용하여 분기처리를 할때.

CASE만큼 좋은 구문은 없다.

오라클의 DECODE도 좋은 선택이다.

샘플데이터.

EMPLOYEE_ID|EMPLOYEE_NAME|JOB      |MANAGER_ID|HIREDATE           |SALARY|COMMISSION|DEPARTMENT_ID|
-----------|-------------|---------|----------|-------------------|------|----------|-------------|
       7521|WARD         |SALESMAN |      7698|1981-02-22 00:00:00|  1250|       500|           30|
       7566|JONES        |MANAGER  |      7839|1981-04-02 00:00:00|  2975|          |           20|
       7654|MARTIN       |SALESMAN |      7698|1981-09-28 00:00:00|  1250|      1400|           30|
       7698|BLAKE        |MANAGER  |      7839|1981-05-01 00:00:00|  2850|          |           30|
       7782|CLARK        |MANAGER  |      7839|1981-06-09 00:00:00|  2450|          |           10|
       7788|SCOTT        |ANALYST  |      7566|0087-02-13 00:00:00|  3000|          |           20|
       7839|KING         |PRESIDENT|          |1981-11-17 00:00:00|  5000|          |           10|
       7369|SMITH        |CLERK    |      7902|1980-12-17 00:00:00|   800|          |           20|
       7499|ALLEN        |SALESMAN |      7698|1981-02-20 00:00:00|  1600|       300|           30|
       7844|TURNER       |SALESMAN |      7698|1981-09-08 00:00:00|  1500|         0|           30|
       7876|ADAMS        |CLERK    |      7788|0087-02-13 00:00:00|  1100|          |           20|
       7900|JAMES        |CLERK    |      7698|1981-12-03 00:00:00|   950|          |           30|
       7902|FORD         |ANALYST  |      7566|1981-12-03 00:00:00|  3000|          |           20|
       7934|MILLER       |CLERK    |      7782|1982-01-23 00:00:00|  1300|          |           10|

CASE.

SQL문에서 IF-THEN-ELSE-END의 논리를 사용할 수 있다.

doc.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm

사용방법.

CASE { simple_case_expression
     | searched_case_expression
     }
     [ else_clause ]
     END

예시.

급여가 2000 초과면 High

, 1000 이상이고 2000 이하면 Medium

, 1000 이하이면 Low인 직원 리스트를 구해보자.

SELECT EMPLOYEE_NAME
     , CASE 
         WHEN SALARY > 2000 THEN 'High'
         WHEN SALARY BETWEEN 1000 AND 2000 THEN 'Medium'
         WHEN SALARY < 1000 THEN 'Low'
        END AS GRADE
FROM EMPLOYEES

결과.

EMPLOYEE_NAME|GRADE |
-------------|------|
WARD         |Medium|
JONES        |High  |
MARTIN       |Medium|
BLAKE        |High  |
CLARK        |High  |
SCOTT        |High  |
KING         |High  |
SMITH        |Low   |
ALLEN        |Medium|
TURNER       |Medium|
ADAMS        |Medium|
JAMES        |Low   |
FORD         |High  |
MILLER       |Medium|

응용예시.

급여가 2000 초과면 High

, 1000 이상이고 2000 이하면 Medium

, 1000 이하이면 Low인 직원 수를 구해보자.

SELECT GRADE
     , COUNT(GRADE)
FROM (
	SELECT CASE 
	         WHEN SALARY > 2000 THEN 'High'
	         WHEN SALARY BETWEEN 1000 AND 2000 THEN 'Medium'
	         WHEN SALARY < 1000 THEN 'Low'
	        END AS GRADE
	FROM EMPLOYEES
)
GROUP BY GRADE;

결과.

GRADE |COUNT(GRADE)|
------|------------|
High  |           6|
Low   |           2|
Medium|           6|