Explain conditional logic in a SELECT statement

You want to perform IF-ELSE operations on values in your SELECT statement. For
example, you would like to produce a result set such that if an employee is paid
$2,000 or less, a message of UNDERPAID is returned; if an employee is paid $4,000
or more, a message of “OVERPAID” is returned; and if they make it somewhere in
between, then OK is returned.

Staff Table

empno ename job mgr_no hiredate sal commission deptno
7369 ALI CLERK 7902 1980-12-17 800.00 NULL 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 2975.00 NULL 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 NULL 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 NULL 10
7788 RAHUL ANALYST 7566 1982-12-09 3000.00 NULL 20
7839 RAJ PRESIDENT NULL 1981-11-17 5000.00 NULL 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAM CLERK 7788 1983-01-12 1100.00 NULL 20
7900 JAMES CLERK 7698 1981-12-03 950.00 NULL 30
7902 ARHAM ANALYST 7566 1981-12-03 3000.00 NULL 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 NULL 10

Example

Use the CASE expression to perform conditional logic directly in your SELECT
statement:

SELECT ename,sal,
CASE WHEN sal <= 2000 then 'UNDERPAID'
WHEN sal >= 4000 then 'OVERPAID'
ELSE 'OK'
END as status
FROM Staff

Output

ename sal status
ALI 800 UNDERPAID
ALLEN 1600 UNDERPAID
WARD 1250 UNDERPAID
JONES 2975 OK
MARTIN 1250 UNDERPAID
BLAKE 2850 OK
CLARK 2450 OK
RAHUL 3000 OK
RAJ 5000 OVERPAID
TURNER 1500 UNDERPAID
ADAM 1100 UNDERPAID
JAMES 950 UNDERPAID
ARHAM 3000 OK
MILLER 1300 UNDERPAID

Discussion

The CASE expression allows you to perform condition logic on values returned by a
query. You can provide an alias for a CASE expression to return a more readable
result set. In the solution, you’ll see the alias STATUS given to the result of the CASE
expression. The ELSE clause is optional. Omit the ELSE, and the CASE expression
will return NULL for any row that does not satisfy the test condition.