SQL CASE

Add if-then-else logic to a query with CASE, to label rows, create categories and count conditionally.

Lesson 7 of 102Intermediate18 min read

Sometimes a value needs a label rather than a number. A product with no stock should say "Sold out", a price over 20 might be "Premium", and a customer who joined this year might be "New". Programming languages handle this with if and else. SQL has the CASE expression.

CASE looks at each row, checks one or more conditions in order, and returns the value for the first one that's true. Because it's an expression, you can use it anywhere a value is allowed: in the SELECT list to create a new column, in ORDER BY to sort in a custom order, and even inside aggregate functions to count only the rows that match a condition.

This lesson shows both forms of CASE, then the two patterns professionals use most: putting rows into categories, and conditional aggregation, which builds a small report with several counts in one query.

The searched CASE

The most flexible form tests a full condition on each WHEN line:

SQL
CASE
    WHEN stock = 0 THEN 'Sold out'
    WHEN stock < 20 THEN 'Low stock'
    ELSE 'In stock'
END
  • Conditions are checked from top to bottom, and the first true one wins, so order them from most specific to least.
  • ELSE provides a value when nothing matched. Without it, unmatched rows get NULL.
  • Every CASE must finish with END. Give the result a name with AS, just like any calculated column.

The simple CASE

When you're comparing one column against several exact values, the simple form is shorter:

SQL
CASE category
    WHEN 'Stationery' THEN 'School'
    WHEN 'Bags' THEN 'School'
    WHEN 'Kitchen' THEN 'Home'
    ELSE 'Other'
END

It can only test equality. For ranges, LIKE patterns or IS NULL, use the searched form, because WHEN NULL never matches anything.

CASE in ORDER BY and GROUP BY

Because CASE produces a value, you can sort or group by it.

  • Custom sort order: ORDER BY CASE WHEN stock = 0 THEN 1 ELSE 0 END, name puts sold-out products last and sorts the rest alphabetically.
  • Grouping into bands: wrap the same CASE in GROUP BY (or group by its alias, which most databases allow) to count how many products fall into each price band.

This turns raw numbers into categories that people actually think in, such as "budget", "standard" and "premium".

Conditional aggregation

Put a CASE inside an aggregate function to count or total only some rows:

SQL
SELECT
    SUM(CASE WHEN stock = 0 THEN 1 ELSE 0 END) AS sold_out,
    SUM(CASE WHEN price >= 20 THEN 1 ELSE 0 END) AS premium
FROM products;

Each CASE returns 1 for matching rows and 0 for the rest, so SUM counts the matches. This lets one query produce several counts side by side, which is exactly what dashboards and reports need. PostgreSQL also supports the shorter COUNT(*) FILTER (WHERE stock = 0).

Keep result types consistent

Every THEN and ELSE in one CASE should return the same kind of value. Mixing types, such as a number in one branch and text in another, is an error in PostgreSQL and leads to confusing automatic conversions in other databases:

SQL
CASE
  WHEN stock = 0 THEN 'Sold out'
  WHEN stock < 20 THEN 'Low stock'
  ELSE 'In stock'
END AS stock_label

Here every branch returns text. If you need a number in one branch and a label in another, return text throughout, for example CAST(stock AS TEXT), or split them into two columns. Remember too that a CASE without ELSE returns NULL for unmatched rows, which then affects sorting and counts.

SQL CASE Syntax

CASE

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END AS alias

CASE column
    WHEN value1 THEN result1
    ELSE default_result
END

SQL CASE Summary

  • CASE WHEN condition THEN value ... ELSE value END adds if-then-else logic to a query.
  • The first true WHEN wins; without ELSE, unmatched rows get NULL.
  • The simple form CASE column WHEN value THEN ... only tests equality.
  • CASE can be used in SELECT, ORDER BY and GROUP BY.
  • SUM(CASE WHEN ... THEN 1 ELSE 0 END) counts rows that meet a condition.

SQL CASE Examples

Each example shows the code, the output it produces and what each part does.

Example 1

Stock labels

SQL
SELECT name,
       stock,
       CASE
           WHEN stock = 0 THEN 'Sold out'
           WHEN stock < 20 THEN 'Low stock'
           ELSE 'In stock'
       END AS availability
FROM products;
Output
name         | stock | availability
-------------+-------+-------------
Notebook     | 120   | In stock
Pen          | 300   | In stock
Backpack     | 15    | Low stock
Water bottle | 40    | In stock
Lunch box    | 0     | Sold out
Desk lamp    | 22    | In stock
Line-by-line explanation
  1. Line 4Checked first, so a stock of 0 never reaches the "Low stock" test.
  2. Line 5Only rows that weren't sold out get here.
  3. Line 6Everything else.
Example 2

Price bands with GROUP BY

SQL
SELECT CASE
           WHEN price < 5 THEN 'Budget'
           WHEN price < 20 THEN 'Standard'
           ELSE 'Premium'
       END AS band,
       COUNT(*) AS products,
       ROUND(AVG(price), 1) AS avg_price
FROM products
GROUP BY band
ORDER BY avg_price;
Output
band     | products | avg_price
---------+----------+----------
Budget   | 2        | 2.4
Standard | 2        | 10.4
Premium  | 2        | 29.7
Line-by-line explanation
  1. Lines 1 to 5Each product is placed in one of three bands.
  2. Line 9Group by the alias of the CASE expression (supported by SQLite, MySQL and PostgreSQL).
  3. Line 10Sort the bands from cheapest to most expensive.
Example 3

A one-row stock report

SQL
SELECT COUNT(*)                                        AS total,
       SUM(CASE WHEN stock = 0 THEN 1 ELSE 0 END)      AS sold_out,
       SUM(CASE WHEN stock BETWEEN 1 AND 19 THEN 1 ELSE 0 END) AS low,
       SUM(CASE WHEN price >= 20 THEN 1 ELSE 0 END)    AS premium
FROM products;
Output
total | sold_out | low | premium
------+----------+-----+--------
6     | 1        | 1   | 2
Line-by-line explanation
  1. Line 21 for the lunch box, 0 for the others, added up.
  2. Line 3Only the backpack has between 1 and 19 in stock.
  3. Line 4The backpack and the desk lamp cost 20 or more.

Common SQL CASE Mistakes

Forgetting END

Don't
SELECT CASE WHEN stock = 0 THEN 'Sold out' ELSE 'OK' AS status
FROM products;
Do
SELECT CASE WHEN stock = 0 THEN 'Sold out' ELSE 'OK' END AS status
FROM products;

Testing NULL in a simple CASE

Don't
CASE city WHEN NULL THEN 'Unknown' ELSE city END
Do
CASE WHEN city IS NULL THEN 'Unknown' ELSE city END

NULL is never equal to anything, so WHEN NULL never matches. COALESCE(city, 'Unknown') is shorter still.

Conditions in the wrong order

Don't
CASE
    WHEN price >= 5 THEN 'Standard'
    WHEN price >= 20 THEN 'Premium'   -- never reached
END
Do
CASE
    WHEN price >= 20 THEN 'Premium'
    WHEN price >= 5 THEN 'Standard'
END

SQL CASE in Real Projects

CASE in real reports

Sales teams want orders labelled "small", "medium" and "large", support teams want tickets grouped by how long they've been open, and finance wants one row showing paid, pending and refunded totals side by side. CASE expressions handle all of these directly in the database, so the numbers are consistent wherever the query is used.

SQL CASE FAQ

Can I use CASE in a WHERE clause?

Yes, but it's rarely needed. A filter such as WHERE CASE WHEN category = 'Bags' THEN price > 20 ELSE price > 5 END can usually be written more clearly with AND and OR: WHERE (category = 'Bags' AND price > 20) OR (category <> 'Bags' AND price > 5).

Does SQL have an IF function?

Standard SQL uses CASE, which works in every database. MySQL also has IF(condition, a, b) and SQL Server has IIF(condition, a, b), which are shortcuts for a two-way CASE. Use CASE when your queries might run on more than one database.

Can I put a CASE inside another CASE?

Yes, a THEN or ELSE can contain another CASE. Deep nesting gets hard to read, though; often a single searched CASE with combined conditions, such as WHEN category = 'Kitchen' AND price > 10, says the same thing more clearly.

Can I use CASE in an UPDATE?

Yes. UPDATE products SET price = CASE WHEN category = 'Kitchen' THEN price * 0.9 ELSE price END; changes different rows by different amounts in a single statement. Include a final ELSE that keeps the current value, or unmatched rows are set to NULL.

Is CASE the same as a switch statement in programming?

It's similar: CASE checks conditions in order and returns the result of the first match, like switch or an if/else if chain. The difference is that CASE is an expression that produces a value inside a query, rather than a statement that runs blocks of code.

SQL CASE Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the expression so it ends correctly.

SELECT name,
       CASE WHEN price > 10 THEN 'Pricey' ELSE 'Cheap'  AS label
FROM products;
Predict the outputeasy

What label does the Pen (price 1.20) get?

CASE
    WHEN price >= 20 THEN 'High'
    WHEN price >= 5 THEN 'Mid'
    ELSE 'Low'
END
Predict the outputmedium

What does a CASE with no ELSE return when no WHEN matches?

Predict the outputmedium

What does this return for the example products?

SELECT SUM(CASE WHEN category = 'Kitchen' THEN 1 ELSE 0 END) FROM products;
Multiple choicehard

Which ORDER BY puts sold-out products last, then sorts by name?

SQL CASE Quiz

5 questions to check what you learned.

  1. What keyword must end every CASE expression?

    • END
    • STOP
    • DONE
    • ENDCASE

  2. If two WHEN conditions are both true for a row, which result is used?

    • The first one listed
    • The last one listed
    • Both, joined together
    • Neither, it returns NULL

  3. Where can a CASE expression be used?

    • Only in SELECT
    • Only in WHERE
    • Only inside aggregate functions
    • Anywhere a value is allowed, such as SELECT, ORDER BY and GROUP BY

  4. What does SUM(CASE WHEN x > 0 THEN 1 ELSE 0 END) calculate?

    • The total of x
    • The average of x
    • The number of rows where x is greater than 0
    • Whether any row has x > 0

  5. Why can't a simple CASE use WHEN NULL?

    • NULL isn't allowed in CASE
    • The simple form tests equality, and nothing is equal to NULL
    • It must be written as WHEN 'NULL'
    • It only works in MySQL

Related SQL topics

06FunctionsTransform values inside a query with string, number, date and NULL-handling functions.05ORDER BY & GROUP BYSort results, return only the top rows, and summarise data with COUNT, SUM, AVG and GROUP BY.03WHERE (Filtering)Return only the rows you need with comparisons, AND, OR, NOT, IN, BETWEEN, LIKE and IS NULL.
Esc
↑↓ to navigateEnter to open