CASE Statement
The CASE expression performs if-then-else logic in SQL.
Two types of CASE expressions:
- Simple case expressions use expressions to determine the returned value
- Searched case expressions use conditions to determine the returned value
Simple CASE Expressions
Simple CASE expressions have the following syntax:
CASE search_expression
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
ELSE default_result
END
Example:
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> SELECT empno, ename,
2 CASE deptno
3 WHEN 10 THEN 'Computer'
4 WHEN 20 THEN 'PC'
5 WHEN 30 THEN 'iPad'
6 WHEN 40 THEN 'iPhone'
7 ELSE 'Mac'
8 END
9 FROM emp;
EMPNO ENAME CASEDEPT
---------- ---------- --------
1 SMITH PC
2 ALLEN iPad
3 WARD iPad
4 JONES PC
5 MARTIN iPad
6 BLAKE iPad
7 CLARK Computer
8 SCOTT PC
9 KING Computer
10 TURNER iPad
11 ADAMS PC
11 rows selected.
SQL>
SQL>
SQL>
Searched CASE Expressions
Searched CASE expressions use conditions to determine the returned value. Searched CASE expressions have the following syntax:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionN THEN resultN
ELSE default_result
END
Example:
SQL> SELECT empno, ename,
2 CASE
3 WHEN deptno = 10 THEN 'Book'
4 WHEN deptno = 20 THEN 'iBook'
5 WHEN deptno = 30 THEN 'iPhone'
6 WHEN deptno = 40 THEN 'iPad'
7 ELSE 'Mac'
8 END
9 FROM emp;
EMPNO ENAME CASEWH
---------- ---------- ------
1 SMITH iBook
2 ALLEN iPhone
3 WARD iPhone
4 JONES iBook
5 MARTIN iPhone
6 BLAKE iPhone
7 CLARK Book
8 SCOTT iBook
9 KING Book
10 TURNER iPhone
11 ADAMS iBook
11 rows selected.
SQL>
Comparison operators in a searched CASE expression
Example:
SQL> SELECT ename, sal,
2 CASE
3 WHEN sal > 1500 THEN 'Above 1500'
4 ELSE 'Less than 1500'
5 END
6 FROM emp;
ENAME SAL CASEWHENSAL>15
---------- ---------- --------------
SMITH 800 Less than 1500
ALLEN 1600 Above 1500
WARD 1250 Less than 1500
JONES 2975 Above 1500
MARTIN 1250 Less than 1500
BLAKE 2850 Above 1500
CLARK 2850 Above 1500
SCOTT 3000 Above 1500
KING 3000 Above 1500
TURNER 1500 Less than 1500
ADAMS 1500 Less than 1500
11 rows selected.
SQL>