Recipe1.8.Using Conditional Logic in a SELECT Statement

Recipe 1.8. Using Conditional Logic in a SELECT Statement

Problem

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 $2000 or less, a message of "UNDERPAID" is returned, if an employee is paid $4000 or more, a message of "OVERPAID" is returned, if they make somewhere in between, then "OK" is returned. The result set should look like this:

 ENAME             SAL  STATUS ---------- ----------  --------- SMITH             800  UNDERPAID ALLEN            1600  UNDERPAID WARD             1250  UNDERPAID JONES            2975  OK MARTIN           1250  UNDERPAID BLAKE            2850  OK CLARK            2450  OK SCOTT            3000  OK KING             5000  OVERPAID TURNER           1500  UNDERPAID ADAMS            1100  UNDERPAID JAMES             950  UNDERPAID FORD            3000   OK MILLER          1300   UNDERPAID 

Solution

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

 1 select ename,sal, 2        case when sal <= 2000 then 'UNDERPAID' 3             when sal >= 4000 then 'OVERPAID' 4             else 'OK' 5        end as status 6   from emp 

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.