SQL CASE Expression

Summary: in this tutorial, you will learn how to use the SQL CASE expression to add if-else logic to the SQL statements.

Introduction to SQL CASE expression #

The CASE expression allows you to add if-else logic to queries, making them more powerful. The CASE expression has two forms:

• Simple CASE expression.
• Searched CASE expression.

SQL allows you to use the CASE expression in the places where you can use an expression. For example, you can use the CASE expression in the clauses such as SELECT, ORDER BY, and HAVING of the SELECT, DELETE, and UPDATE statements.

Simple CASE expression #

Here’s the syntax of the simple CASE expression:

CASE expression
  WHEN when_expression_1 THEN result_1
  WHEN when_expression_2 THEN result_2
  WHEN when_expression_3 THEN result_3
  ELSE else_result
ENDCode language: SQL (Structured Query Language) (sql)

The CASE expression compares an expression to a set of expressions (when_expression_1, when_expression_2, when_expression_3, …) using the equality operator (=).

The CASE statement returns the result_1, result_2, or result_3 if the expression matches the corresponding expression in the WHEN clause.

If the expression does not match any expression in the WHEN clause, it returns the else_result in the ELSE clause.

The ELSE clause is optional. If you omit the ELSE clause and the expression does not match any expression in the WHEN clauses, the CASE expression returns NULL.

Simple CASE expression example #

Let’s take a look at the employees table.

The following statement uses a simple CASE expression to get the work anniversaries of employees in the year of 2000:

SELECT
  first_name,
  last_name,
  hire_date,
  CASE 2000 - EXTRACT(YEAR FROM hire_date)
    WHEN 1 THEN '1 year'
    WHEN 3 THEN '3 years'
    WHEN 5 THEN '5 years'
    WHEN 10 THEN '10 years'
    WHEN 15 THEN '15 years'
    WHEN 20 THEN '20 years'
    WHEN 25 THEN '25 years'
    WHEN 30 THEN '30 years'
  END anniversary
FROM
  employees
ORDER BY
  first_name;Code language: SQL (Structured Query Language) (sql)

Try it

 first_name  |  last_name  | hire_date  | anniversary
-------------+-------------+------------+------------
 Adam        | Fripp       | 1997-04-10 | 3 years
 Alexander   | Hunold      | 1990-01-03 | 10 years
 Alexander   | Khoo        | 1995-05-18 | 5 years
 Britney     | Everett     | 1997-03-03 | 3 years
 Bruce       | Ernst       | 1991-05-21 | NULL
 Charles     | Johnson     | 2000-01-04 | NULL
 Daniel      | Faviet      | 1994-08-16 | NULL
...Code language: SQL (Structured Query Language) (sql)

The EXTRACT function extracts the year from the hire date. The following expression subtracts the hire year from the year 2000 to get the total number of years an employee has worked for the company until 2000:

2000 - EXTRACT(YEAR FROM hire_date)Code language: SQL (Structured Query Language) (sql)

The CASE expression compares the years of service with 1, 3, 5, 10, 15, 20, 25, and 30.

If the years of service equals one of these numbers, it returns the work anniversary of the employee. Otherwise, it returns NULL.

To retrieve employees who have a work anniversary in 2000, you can filter out NULL from the result set:

WITH work_anniversary_employees AS (
SELECT
  first_name,
  last_name,
  hire_date,
  CASE (2000 - EXTRACT(YEAR FROM hire_date))
    WHEN 1 THEN '1 year'
    WHEN 3 THEN '3 years'
    WHEN 5 THEN '5 years'
    WHEN 10 THEN '10 years'
    WHEN 15 THEN '15 years'
    WHEN 20 THEN '20 years'
    WHEN 25 THEN '25 years'
    WHEN 30 THEN '30 years'
  END anniversary
FROM
  employees
) 
SELECT first_name, last_name, hire_date, anniversary
FROM  work_anniversary_employees
WHERE anniversary IS NOT NULL;Code language: SQL (Structured Query Language) (sql)

Output:

 first_name | last_name  | hire_date  | anniversary
------------+------------+------------+-------------
 Alexander  | Hunold     | 1990-01-03 | 10 years
 David      | Austin     | 1997-06-25 | 3 years
 Diana      | Lorentz    | 1999-02-07 | 1 year
 John       | Chen       | 1997-09-28 | 3 years
 Ismael     | Sciarra    | 1997-09-30 | 3 years
 Luis       | Popp       | 1999-12-07 | 1 year
...Code language: SQL (Structured Query Language) (sql)

Searched CASE expression #

Here’s the syntax of the searched CASE expression:

CASE
  WHEN boolean_expression_1 THEN result_1
  WHEN boolean_expression_2 THEN result_2
  WHEN boolean_expression_3 THEN result_3
  ELSE else_result
END;Code language: SQL (Structured Query Language) (sql)

In this syntax, the CASE expression evaluates the boolean expressions in each WHEN clause from top to bottom.

If the expression is true, the searched CASE statement returns the result in the corresponding THEN clause.

If no expression evaluates to true, the CASE expression returns the else_result in the ELSE clause.

Like the simple CASE expression, the ELSE clause is optional. If you omit it and no Boolean expression evaluates to true, the CASE expression returns NULL.

Note that the simple CASE expression evaluates one boolean expression whereas the searched CASE expression evaluates multiple boolean expressions.

Searched CASE expression example #

The following query uses the searched CASE expression to rank employee’s salary:

SELECT
  first_name,
  last_name,
  CASE
    WHEN salary < 3000 THEN 'Low'
    WHEN salary >= 3000 AND salary <= 5000 THEN 'Medium'
    WHEN salary > 5000 THEN 'High'
  END salary_ranking
FROM
  employees
ORDER BY
  first_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name  |  last_name  | salary_ranking
-------------+-------------+----------------
 Adam        | Fripp       | High
 Alexander   | Hunold      | High
 Alexander   | Khoo        | Medium
 Britney     | Everett     | Medium
 Bruce       | Ernst       | High
 Charles     | Johnson     | High
 Daniel      | Faviet      | High
 David       | Austin      | Medium
...Code language: SQL (Structured Query Language) (sql)

In this example:

• If the salary is less than 3000, the CASE expression returns “Low“.
• If the salary is between 3000 and 5000, it returns “Medium“.
• When the salary is greater than 5000, the CASE expression returns “High“.

Summary #

• Use the CASE expression to add if-else logic to your query.
• Use simple CASE expression to evaluate one expression.
• Use searched CASE expression to evaluate multiple expressions.

Databases #

• PostgreSQL CASE Expression
• MySQL CASE Expression
• SQLite CASE Expression
• Oracle CASE Expression
• SQL Server CASE Expression
• Db2 CASE Expression

Quiz #