SQL query: Using Conditional Logic in a SELECT Statement

SQL studing: CASE Using Conditional Logic

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

 

Вас заинтересует / Intresting for you:

A Brief History of SQL
A Brief History of SQL 2866 views Александров Попков Wed, 17 Oct 2018, 15:04:29
Referencing an Aliased Column ...
Referencing an Aliased Column ... 248 views Денис Wed, 14 Jul 2021, 12:59:03
SQL: how to determine which ro...
SQL: how to determine which ro... 175 views Денис Tue, 06 Jul 2021, 18:47:53
How to Retrieving a Subset of ...
How to Retrieving a Subset of ... 251 views Денис Wed, 14 Jul 2021, 04:45:37