SQL CASE Expression

A special scalar expression in SQL language is CASE expression. SQL CASE expression is used as a kind of IF-THEN-ELSE statement. It is similar to switch statement in modern programming language such as Java or C#. The syntax of the CASE statement is simple as follows :

CASE column_name
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  ELSE result
END

The data type of the column_name after the CASE must be the same as the data type of the expression followed by the keyword THEN or ELSE. The ELSE part of the case expression is optional. If the ELSE part is omitted and all the conditions in the WHEN does not meet, the CASE expression will return NULL.

The case expression can be used in anywhere scalar expressions are allowed, including in WHERE and HAVING clause of the select statement.

It is more intuitive to demonstrate CASE expression through an example. Here is the employees table for demonstration :

employee_id  name      department_id  job_id  salary 
-----------  --------  -------------  ------  -------
          3  newcomer         (NULL)       0  2000.00
          2  mary                  2       2  2500.00
          5  Tom                   2       2  2700.00
          4  anna                  1       1  2800.00
          1  jack                  1       1  3000.00
          6  foo                   3       3  4700.00

We can use the CASE expression to print out the employee name, his salary and a computed column which is called salary level. Here is the sql query:

SELECT name,salary,
CASE    
WHEN  salary <= 2000 THEN 'low'
WHEN  salary > 2000 AND salary <= 3000 THEN 'average'
WHEN  salary > 3000 THEN 'high'
END AS salary_level
FROM employees
ORDER BY salary ASC

The logic is simple if the salary of the employee lower than 2000 the salary level is low; greater than 2000 and less than or equal to 300, the salary level is average; And greater than 3000, the salary level is high (of course just for example). And the output is :

name       salary  salary_level
--------  -------  ------------
newcomer  2000.00  low         
mary      2500.00  average     
Tom       2700.00  average     
anna      2800.00  average     
jack      3000.00  average     
foo       4700.00  high