SQL - 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.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table