CASE expressions

If this, then that

A good archivist doesn't just read data — they label it.

Kotomarket's old archive contains raw facts:

• a product's price;
• an order's status;
• the quantity in stock;
• a user's city;
• an event's date.

But people often need more than raw facts — they need clear, meaningful labels.

For example, the products table stores each product's price:

The price is useful on its own, but in a report you may also want to see each product's segment at a glance:

That kind of labelling is what CASE does.

CASE is an expression made up of branches:

CASE
  WHEN condition1 THEN value1
  WHEN condition2 THEN value2
  ELSE default_value
END

It reads almost like a plain-English instruction:

if condition 1 is true, return value 1;
otherwise, if condition 2 is true, return value 2;
otherwise, return the default value.

Here's an example:

SELECT
    name,
    price,
    CASE
      WHEN price < 1500 THEN 'cheap'
      WHEN price < 4000 THEN 'medium'
      ELSE 'expensive'
    END AS segment
FROM products;

Here CASE creates a new computed column, segment.

It doesn't change any data in the table.
It simply adds a label to the query result.

QUERY: A label is an archivist's interpretation layered over the facts. Choose your labels carefully — you'll want to stand by them a hundred years from now.

CASE checks the branches from top to bottom

Here's the most important thing to remember about CASE:

the first matching WHEN wins.

As soon as SQL finds a branch whose condition evaluates to TRUE, it returns the value after THEN and stops checking the remaining branches.

Look at this example:

CASE
  WHEN price < 4000 THEN 'not expensive'
  WHEN price < 1500 THEN 'cheap'
  ELSE 'expensive'
END

At first glance it looks as if a product priced at 900 should get the label 'cheap'.

But it won't.

Why?

At a price of 900, the first condition is already true:

price < 4000

So CASE immediately returns:

not expensive

The next branch:

WHEN price < 1500 THEN 'cheap'

is never reached.

That is why branch order isn't merely cosmetic. It is part of the logic.

When defining ranges like these, put the more restrictive condition first:

CASE
  WHEN price < 1500 THEN 'cheap'
  WHEN price < 4000 THEN 'medium'
  ELSE 'expensive'
END

That way, a product priced at 900 matches the first branch, while one priced at 2500 matches the second.

What ELSE does

ELSE is the fallback.

SQL uses it when none of the WHEN conditions match.

For example:

CASE
  WHEN price < 1500 THEN 'cheap'
  WHEN price < 4000 THEN 'medium'
  ELSE 'expensive'
END

If the price is 7200, neither of the first two conditions is true:

7200 < 1500  -- no
7200 < 4000  -- no

So the value from ELSE is returned:

expensive

And what happens if you don't write an ELSE?

CASE
  WHEN price < 1500 THEN 'cheap'
  WHEN price < 4000 THEN 'medium'
END

If the price is 7200, no WHEN condition matches. There is no fallback.

In that case, CASE returns NULL.

In other words:

if no branch matches and ELSE is omitted, the result is NULL.

In reports, it is often better to include an explicit ELSE so the result is easier to understand:

ELSE 'no segment'

or:

ELSE 'expensive'

The choice depends on your business logic.

CASE is an expression, not a separate command

CASE returns a value.

That means you can use it wherever SQL expects a value:

• in SELECT;
• in ORDER BY;
• sometimes in WHERE;
• inside calculations;
• inside aggregates, which we'll cover in later modules.

In this lesson, we'll focus on using it in SELECT.

SELECT
    name,
    price,
    CASE
      WHEN price < 1500 THEN 'cheap'
      WHEN price < 4000 THEN 'medium'
      ELSE 'expensive'
    END AS segment
FROM products;

Here, CASE creates a computed column.

You can think of it like this:

for every row, compute a new value from the conditions

Each row gets its own result.

The product priced at 900 gets 'cheap'.
The one priced at 2500 gets 'medium'.
The one priced at 7200 gets 'expensive'.

CASE doesn't filter rows on its own. It has a different job from WHERE.

WHERE decides:

keep the row or drop it?

CASE decides:

which value to show for this row?

CASE for business labels

CASE is often used to turn technical values into human-friendly labels.

For example, the orders table has statuses:

These values work well in a database: they are short, stable, and consistently spelled.

But a report can display them as human-friendly labels:

SELECT
    id,
    status,
    CASE
      WHEN status = 'paid' THEN 'Paid'
      WHEN status = 'pending' THEN 'Awaiting payment'
      WHEN status = 'cancelled' THEN 'Cancelled'
      WHEN status = 'refunded' THEN 'Refunded'
      ELSE 'Unknown status'
    END AS status_label
FROM orders;

The result:

This query doesn't change status in the table.

It simply adds a readable label alongside it.

That is useful in reports, exports, dashboards, and course exercises — anywhere the result needs to be easy for people to understand.

The searched form of CASE

The form we used above is called the searched form:

CASE
  WHEN condition1 THEN value1
  WHEN condition2 THEN value2
  ELSE default_value
END

The SQL standard calls this a searched CASE.

After each WHEN, you can write a complete condition:

WHEN price < 1500 THEN 'cheap'

WHEN status = 'paid' THEN 'Paid'

WHEN stock = 0 THEN 'out of stock'

WHEN price < 1500 AND stock > 0 THEN 'cheap and in stock'

The searched form is flexible. You can use it to:

• compare different columns;
• use AND and OR;
• check for NULL with IS NULL;
• build ranges;
• express complex business rules.

For example:

SELECT
    name,
    price,
    stock,
    CASE
      WHEN stock = 0 THEN 'out of stock'
      WHEN price < 1500 AND stock > 0 THEN 'cheap item in stock'
      WHEN price >= 1500 AND stock > 0 THEN 'item in stock'
      ELSE 'check the data'
    END AS product_note
FROM products;

Here, each branch has its own condition.

Beginners often find the searched form easier because the full logic is visible right in the query.

The simple form of CASE

CASE also has a simple form.

It is useful when you want to compare a single expression with several exact values.

For example, instead of writing this:

CASE
  WHEN status = 'paid' THEN 'Paid'
  WHEN status = 'pending' THEN 'Awaiting'
  WHEN status = 'cancelled' THEN 'Cancelled'
  WHEN status = 'refunded' THEN 'Refunded'
  ELSE 'Other status'
END

you can use the shorter form:

CASE status
  WHEN 'paid' THEN 'Paid'
  WHEN 'pending' THEN 'Awaiting'
  WHEN 'cancelled' THEN 'Cancelled'
  WHEN 'refunded' THEN 'Refunded'
  ELSE 'Other status'
END

Here, you write the expression status just once:

CASE status

Then list the values you want to compare it with:

WHEN 'paid' THEN 'Paid'
WHEN 'pending' THEN 'Awaiting'

The simple form reads like a lookup table:

It is a good fit for straightforward mappings:

if the value matches this, show that label.

Simple vs. searched form: what is the difference

Let's put the two forms side by side.

The searched form:

CASE
  WHEN status = 'paid' THEN 'Paid'
  WHEN status = 'pending' THEN 'Awaiting'
  ELSE 'Other status'
END

The simple form:

CASE status
  WHEN 'paid' THEN 'Paid'
  WHEN 'pending' THEN 'Awaiting'
  ELSE 'Other status'
END

Both forms can produce the same result.

The difference is in how you write them.

In the simple form, SQL compares the expression after CASE with the values after WHEN for you:

status = 'paid'
status = 'pending'

In the searched form, you write out the conditions in full yourself:

WHEN status = 'paid'
WHEN status = 'pending'

The simple form is shorter when every branch compares the same expression with an exact value.

The searched form is more flexible when you need different kinds of conditions.

For example, the simple form can't properly express logic like this:

CASE
  WHEN status IS NULL THEN 'Status not set'
  WHEN status = 'paid' AND paid_at IS NOT NULL THEN 'Paid'
  WHEN status = 'pending' THEN 'Awaiting'
  ELSE 'Check the order'
END

This example checks for NULL, examines another column (paid_at), and uses a compound condition with AND.

For cases like this, use the searched form.

A practical rule:

If you're matching one column against a set of exact values, the simple CASE is a good fit.

If you need ranges, NULL checks, multiple columns, or AND/OR, use the searched CASE.

Why WHEN NULL doesn't work in a simple CASE

The simple form has one important pitfall.

Suppose orders.status is sometimes NULL.

A beginner might write this:

CASE status
  WHEN 'paid' THEN 'Paid'
  WHEN NULL THEN 'Status not set'
  ELSE 'Other status'
END

You might expect this branch:

WHEN NULL THEN 'Status not set'

to run when status is NULL.

But it will never run.

Why?

The simple form, CASE status WHEN ..., compares status with each branch value using ordinary equality, =.

So the WHEN NULL branch effectively becomes:

status = NULL

From the lesson on NULL, you already know that:

status = NULL

doesn't evaluate to TRUE.

It evaluates to UNKNOWN.

A WHEN branch runs only when its condition evaluates to TRUE.

To check for NULL, use the searched form instead:

CASE
  WHEN status IS NULL THEN 'Status not set'
  WHEN status = 'paid' THEN 'Paid'
  WHEN status = 'pending' THEN 'Awaiting'
  ELSE 'Other status'
END

Here, the condition is written correctly:

status IS NULL

The rule to remember:

To check for NULL in a CASE, use IS NULL — and therefore the searched form.

THEN and ELSE values must have compatible types

CASE returns a single value.

SQL must assign that value a single data type.

For example:

CASE
  WHEN price < 1500 THEN 'cheap'
  WHEN price < 4000 THEN 'medium'
  ELSE 'expensive'
END

Every branch returns text, so this works.

'cheap'
'medium'
'expensive'

But this CASE mixes data types:

CASE
  WHEN price < 1500 THEN 'cheap'
  ELSE 0
END

One branch returns text:

'cheap'

The other returns a number:

0

The database must determine a single result type for the CASE: text or numeric. Depending on the database and context, mixing types may cause an error or an unexpected conversion.

A good rule of thumb is:

Make the THEN and ELSE branches return values with compatible data types.

If you are creating a text label, return text in every branch:

CASE
  WHEN price < 1500 THEN 'cheap'
  ELSE 'not cheap'
END

If you are creating a numeric rank, return numbers in every branch:

CASE
  WHEN status = 'paid' THEN 1
  WHEN status = 'pending' THEN 2
  ELSE 3
END

This matters especially in ORDER BY, where CASE often returns a numeric sort priority.

CASE in ORDER BY: using a custom sort order

Sometimes the default sort order doesn't match what you need.

For example, consider these order statuses:

paid
pending
cancelled
refunded

If you sort them alphabetically:

SELECT id, status
FROM orders
ORDER BY status;

you get an alphabetical order that ignores their business meaning.

But your business rules may require a different order:

1. paid orders first;
2. pending orders;
3. cancelled orders;
4. refunded orders;
5. everything else.

Alphabetical sorting does not understand business priorities, but CASE lets you define them explicitly:

SELECT id, status
FROM orders
ORDER BY
  CASE status
    WHEN 'paid' THEN 1
    WHEN 'pending' THEN 2
    WHEN 'cancelled' THEN 3
    WHEN 'refunded' THEN 4
    ELSE 5
  END,
  id;

Here CASE returns a numeric priority:

ORDER BY then sorts by that priority.

The final id gives rows with the same status a consistent order:

ORDER BY CASE ... END, id

That way, rows with the same status are sorted by id instead of appearing in an unspecified order.

You can label and sort with the same logic

Sometimes you'll want to do both at once:

• show a clear, readable label;
• sort the rows in a custom business order.

For example:

SELECT
    id,
    status,
    CASE status
      WHEN 'paid' THEN 'Paid'
      WHEN 'pending' THEN 'Awaiting'
      WHEN 'cancelled' THEN 'Cancelled'
      WHEN 'refunded' THEN 'Refunded'
      ELSE 'Other status'
    END AS status_label
FROM orders
ORDER BY
    CASE status
      WHEN 'paid' THEN 1
      WHEN 'pending' THEN 2
      WHEN 'cancelled' THEN 3
      WHEN 'refunded' THEN 4
      ELSE 5
    END,
    id;

In SELECT, CASE creates a text label:

status_label

In ORDER BY, a second CASE creates a numeric sort priority.

Why not sort by the label itself?

Because the labels' alphabetical order may not match the business logic.

For example, 'Cancelled' comes before 'Paid' alphabetically, even when your business rules require the opposite.

Use text for display and a numeric rank for sorting.

QUERY: CASE does more than say “if this, then that.” It tells the archive how people should interpret the raw facts.