CASE operator in SQL

The CASE operator in SQL is a versatile control flow tool that allows you to perform if-then-else type logic directly within your SQL queries.

This functionality enables the dynamic transformation of data based on specific conditions, making it incredibly useful for creating calculated fields, custom sorting, or implementing conditional logic in data retrieval.

Syntax

The CASE operator can be used in any statement that accepts a valid boolean expression (TRUE/FALSE), such as SELECT, WHERE, ORDER BY, and so on. Here’s the basic CASE operator syntax:

SELECT
  CASE
    WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    ...
    ELSE default_result -- Optional clause
  END AS alias_name
FROM table_name

Applications of CASE

Multi-line syntax

One of the most common uses of the CASE operator is within SELECT clauses, where it can dynamically calculate new columns in the result set based on certain conditions.

Here’s an example query that calculates user language based on their account country:

SELECT
  id,
  email,
  CASE
    WHEN country = 'us' THEN 'english'
    WHEN country = 'de' THEN 'german'
    ELSE 'other'
  END AS user_language
FROM users

One-line syntax

The CASE operator often comes handy when we need to derive a new status column from existing columns.

Here’s an example from a subscription product where we calculate user access tier based on their subscription status column:

SELECT
  CASE WHEN status = 'customer' OR status = 'trial' THEN 'premium' ELSE 'free' END access_tier,
  *
FROM users

Skipping the default value

The ELSE part (the default value when no conditions are matching) of the CASE operator is optional. We can rewrite the last query without it:

SELECT
  CASE WHEN status = 'customer' OR status = 'trial' THEN 'premium' END access_tier,
  *
FROM users

The default value of the CASE statement in this query will be NULL. Essentially, it’s a shorter version of the following query:

SELECT
  CASE WHEN status = 'customer' OR status = 'trial' THEN 'premium' ELSE NULL END access_tier,
  *
FROM users

Every WHEN clause is eventually evaluated into a single TRUE or FALSE, so we can use any combination of AND, OR or boolean functions.

See also

AND operator in SQL

OR operator in SQL