Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 125 ยท Functions and Expressions

CASE Expressions

Add if-then-else logic to SQL with CASE WHEN. Label rows, sort in custom orders, count conditionally, avoid errors and use iif() in SQLite, MySQL and SQL Server.

Sometimes a query needs to make a decision for each row: is this price cheap or expensive? Did the student pass? Which order status should come first? SQL's answer is the CASE expression, the language's built-in if-then-else.

CASE is an expression, not a statement. It produces one value per row, so you can use it anywhere a value is allowed: in the SELECT list, in WHERE, in ORDER BY, inside functions and inside aggregates. That flexibility makes it one of the most useful tools in SQL, and it works the same way in every database.

In this lesson you'll write both forms of CASE (searched and simple), learn the rules that cause most CASE bugs, and use CASE for custom sorting and conditional counting. You'll also meet iif(), the compact shortcut that SQLite, MySQL and SQL Server offer.

Searched CASE: conditions in order

The most common form tests a list of conditions:

SQL
CASE
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  ELSE fallback_result
END

SQL checks the WHEN conditions from top to bottom and returns the result of the first one that is true. If none is true, it returns the ELSE value. Here every outdoor and audio product gets a price band:

SQL Price bands ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Read the second branch carefully: WHEN price < 60 doesn't need AND price >= 20, because any product under 20 was already caught by the first branch. Each WHEN only sees the rows that every earlier branch rejected. Always give the CASE an alias (AS band), or the column header will be the whole expression.

Order matters

Because the first true condition wins, overlapping conditions in the wrong order give wrong answers without any error. This version checks the widest range first:

SQL Branches in the wrong order ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Every product under 120 is now premium, including the 24.00 water bottle. The mid-range and budget branches can never be reached. When your conditions are ranges, sort them from the most specific to the least specific, either all ascending with < or all descending with >=.

Letter grades and the missing ELSE

If you leave out ELSE and no condition matches, CASE returns NULL. Sometimes that's exactly what you want, but it's easy to forget a case. Let's turn the school's numeric grades (0โ€“100) into letter grades for one student, Nico Adeyemi:

SQL Letter grades ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

The grade for course 1 in term 3 hasn't been entered yet, so grade is NULL. Every comparison with NULL is unknown rather than true (see Working with NULL), so the first four branches don't match, and the explicit WHEN grade IS NULL branch labels it. Remove that line and run again: the row gets F, because ELSE catches everything that didn't match. A missing grade becoming a failing grade is exactly the kind of quiet bug to watch for.

Simple CASE: comparing one value

When every branch compares the same expression with =, the simple form is shorter:

SQL
CASE expression
  WHEN value_1 THEN result_1
  WHEN value_2 THEN result_2
  ELSE fallback_result
END

It's ideal for translating codes into labels. SQLite's strftime('%w', ...) returns a weekday number as text ('0' for Sunday), which the Date and Time Functions lesson promised to turn into names:

SQL Simple CASE for labels ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Note the quotes in WHEN '0': strftime() returns text, and in SQLite the text '0' isn't equal to the number 0.

Simple CASE can't detect NULL. CASE x WHEN NULL THEN ... never matches, because it means x = NULL, which is never true. Use the searched form: CASE WHEN x IS NULL THEN ....

Sorting in a custom order

ORDER BY status sorts statuses alphabetically: cancelled, delivered, pending, shipped. A support team would rather see the orders that need work first. A CASE in ORDER BY maps each status to a rank:

SQL Custom sort order ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The CASE values never appear in the output; they only decide the order. The second sort key, ordered_at, orders rows within each status. The same trick can pin one value to the top, for example ORDER BY CASE WHEN country = 'Canada' THEN 0 ELSE 1 END, country. The ORDER BY lesson covers sorting in general.

Counting with CASE inside aggregates

CASE becomes even more powerful inside aggregate functions such as sum() and count(). Because CASE runs once per row, you can turn each row into a 1 or a 0 and add them up. That gives several conditional counts in a single pass over the table:

SQL Conditional counts ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The shop has 120 orders: 57 delivered and 17 cancelled. Two patterns are at work here:

  • sum(CASE WHEN ... THEN 1 ELSE 0 END) adds 1 for every matching row.
  • count(CASE WHEN ... THEN 1 END) works because count(expression) skips NULLs, and the missing ELSE makes non-matching rows NULL.

This technique is called conditional aggregation. Combined with GROUP BY it turns rows into columns, which you'll do in Pivoting Rows into Columns. The aggregate functions themselves are covered in Aggregate Functions.

CASE prevents errors

SQL evaluates a CASE's branches only as needed: once a WHEN is true, the later branches are skipped. You can use that to guard against bad input. A ratio with a zero denominator, for example:

SQL
SELECT
  name,
  CASE
    WHEN stock = 0 THEN NULL
    ELSE round(price / stock, 2)
  END AS price_per_unit_in_stock
FROM products;

In SQLite dividing by zero already returns NULL, but in PostgreSQL and SQL Server it raises an error that stops the whole query. The guard keeps the query portable. The next lesson shows a shorter way to write this specific guard with NULLIF.

iif(): a two-branch shortcut

For a single condition, SQLite (3.32+) has iif(condition, value_if_true, value_if_false):

SQL iif() shortcut ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

iif(a, b, c) means exactly CASE WHEN a THEN b ELSE c END. SQLite 3.48 added if() as another name, and you can pass more arguments for more branches, but plain CASE is clearer once you have more than two outcomes.

CASE across databases

CASE itself is standard SQL and works identically in SQLite, PostgreSQL, MySQL, SQL Server and Oracle. The shortcuts differ:

Shortcut SQLite PostgreSQL MySQL SQL Server Oracle
iif(cond, a, b) yes (3.32+) no no yes (2012+) no
IF(cond, a, b) if() (3.48+) no yes no no
DECODE(x, v1, r1, ..., default) no no no no yes
Boolean expression as a value yes (1/0) yes (true/false) yes (1/0) no 23ai+
SQL
-- MySQL
SELECT name, IF(stock = 0, 'sold out', 'available') AS availability
FROM products;

-- Oracle
SELECT name, DECODE(category_id, 1, 'Kitchen', 2, 'Outdoor', 'Other') AS category
FROM products;

One difference between databases matters inside CASE: the result types. SQLite happily returns text from one branch and a number from another. PostgreSQL requires all branches to have compatible types and raises an error for CASE WHEN x THEN 'none' ELSE 0 END. Returning the same type from every branch keeps your queries portable.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A product costs 15. What does CASE WHEN price < 100 THEN 'A' WHEN price < 20 THEN 'B' ELSE 'C' END return?

    CASE returns the first branch whose condition is true. 15 < 100 is true, so the result is A and the second branch is never checked.
  2. 2. What does a CASE expression return when no WHEN matches and there is no ELSE?

    Without ELSE, a CASE that matches nothing returns NULL.
  3. 3. Why doesn't CASE grade WHEN NULL THEN 'missing' END ever return 'missing'?

    Simple CASE tests grade = NULL, which is unknown. Use the searched form CASE WHEN grade IS NULL THEN 'missing' END.
  4. 4. Which expression counts the cancelled orders when used in a SELECT on orders?

    The CASE turns each cancelled order into 1 and every other order into 0, and sum() adds them up. count(status = 'cancelled') counts every row, because 0 isn't NULL.
  5. 5. What does a CASE expression in ORDER BY do?

    The CASE value is used only for sorting, for example mapping statuses to 1, 2, 3, 4. It doesn't appear in the output.

Summary

  • CASE WHEN condition THEN result ... ELSE fallback END is SQL's if-then-else and produces one value per row.
  • The first true condition wins, so order range checks from most to least specific.
  • Without ELSE, unmatched rows get NULL; handle NULL inputs explicitly with WHEN x IS NULL.
  • Simple CASE (CASE x WHEN value THEN ...) compares one expression with = and can't match NULL.
  • CASE in ORDER BY creates custom sort orders; CASE inside sum() or count() counts conditionally.
  • CASE can guard against errors such as division by zero.
  • iif(cond, a, b) is a shortcut in SQLite and SQL Server (IF() in MySQL); CASE is standard and works everywhere.

Next: COALESCE, IFNULL and NULLIF, where you'll replace missing values and avoid division by zero.