SQL CASE WHEN: Conditional Logic in Queries

Every database holds raw data, but stakeholders want context. They don't want to see 247892 — they want to know whether that invoice is "High," "Medium," or "Low." The SQL CASE expression is how you bridge that gap, letting you add if-then-else logic directly inside a query.

In this guide you'll learn every flavor of CASE WHEN, practice on the Chinook digital-media database, and walk away ready to categorize, label, and transform data on the fly.

Table of Contents

1. What Is CASE WHEN?
2. Simple CASE vs Searched CASE
3. CASE WHEN with Aggregations
4. CASE WHEN in ORDER BY and WHERE
5. Nested CASE and Multiple Conditions
6. Cheat Sheet
7. Common Mistakes
8. Practice Exercises
9. Summary
10. FAQs

What Is CASE WHEN?

CASE WHEN is SQL's built-in conditional expression. It evaluates conditions top-to-bottom and returns a value for the first condition that is true. If nothing matches, the optional ELSE clause kicks in (or NULL if ELSE is omitted).

Syntax:

SELECT
  column_name,
  CASE
    WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    ELSE default_result
  END AS alias
FROM table_name;

Example — Label tracks by price tier:

SELECT
  Name,
  UnitPrice,
  CASE
    WHEN UnitPrice >= 1.99 THEN 'Premium'
    WHEN UnitPrice >= 0.99 THEN 'Standard'
    ELSE 'Budget'
  END AS PriceTier
FROM Track
LIMIT 5;

Tip: Always include an ELSE clause to avoid unexpected NULL values in your results.

Simple CASE vs Searched CASE

SQL supports two forms of CASE. Choosing the right one keeps your queries clean and readable.

Simple CASE

Compares a single expression against a list of values — perfect when you're matching exact values.

SELECT
  MediaTypeId,
  CASE MediaTypeId
    WHEN 1 THEN 'MPEG'
    WHEN 2 THEN 'Protected AAC'
    WHEN 3 THEN 'Protected MPEG-4'
    WHEN 4 THEN 'Purchased AAC'
    WHEN 5 THEN 'AAC'
    ELSE 'Other'
  END AS MediaFormat
FROM Track
LIMIT 5;

Searched CASE

Evaluates independent boolean expressions — use this when conditions involve ranges, multiple columns, or operators beyond =.

SELECT
  FirstName,
  LastName,
  Country,
  CASE
    WHEN Country IN ('USA', 'Canada') THEN 'North America'
    WHEN Country IN ('Brazil', 'Argentina', 'Chile') THEN 'South America'
    WHEN Country IN ('United Kingdom', 'France', 'Germany', 'Czech Republic') THEN 'Europe'
    ELSE 'Other'
  END AS Region
FROM Customer
LIMIT 5;

When to use which? If every condition compares the same column to a literal value, use Simple CASE for brevity. For everything else, use Searched CASE.

CASE WHEN with Aggregations

CASE really shines when combined with aggregate functions like SUM, COUNT, or AVG. This technique — sometimes called conditional aggregation — lets you pivot data without subqueries.

Example — Count tracks by price tier per genre:

SELECT
  g.Name AS Genre,
  COUNT(*) AS TotalTracks,
  SUM(CASE WHEN t.UnitPrice >= 1.99 THEN 1 ELSE 0 END) AS PremiumTracks,
  SUM(CASE WHEN t.UnitPrice < 1.99 THEN 1 ELSE 0 END) AS StandardTracks
FROM Track t
JOIN Genre g ON t.GenreId = g.GenreId
GROUP BY g.Name
ORDER BY TotalTracks DESC
LIMIT 5;

This pattern replaces multiple queries or complex subqueries with a single, efficient scan of the data. It also pairs naturally with GROUP BY and HAVING when you need to filter aggregated results.

CASE WHEN in ORDER BY and WHERE

Custom Sort Order

You can embed a CASE in the ORDER BY clause to define a business-specific sort that doesn't follow alphabetical or numeric order.

SELECT
  FirstName,
  LastName,
  Title
FROM Employee
ORDER BY
  CASE Title
    WHEN 'General Manager' THEN 1
    WHEN 'Sales Manager' THEN 2
    WHEN 'IT Manager' THEN 3
    ELSE 4
  END;

Filtering with CASE

While you can technically use CASE inside a WHERE clause, it is usually cleaner to use standard AND/OR logic. However, CASE in WHERE becomes useful when you need to apply different filter rules based on another column:

SELECT
  Name,
  Milliseconds,
  GenreId
FROM Track
WHERE
  CASE
    WHEN GenreId = 1 THEN Milliseconds > 300000
    WHEN GenreId = 2 THEN Milliseconds > 200000
    ELSE Milliseconds > 250000
  END
LIMIT 5;

Nested CASE and Multiple Conditions

For complex classification logic, you can nest CASE expressions or combine multiple conditions with AND/OR inside a single WHEN.

Example — Classify invoices by value and region:

SELECT
  i.InvoiceId,
  i.Total,
  c.Country,
  CASE
    WHEN i.Total > 15 AND c.Country IN ('USA', 'Canada') THEN 'High-Value NA'
    WHEN i.Total > 15 THEN 'High-Value International'
    WHEN i.Total > 5 THEN 'Medium'
    ELSE 'Low'
  END AS InvoiceCategory
FROM Invoice i
JOIN Customer c ON i.CustomerId = c.CustomerId
ORDER BY i.Total DESC
LIMIT 5;

Best practice: When nesting gets deeper than two levels, consider extracting the logic into a Common Table Expression (CTE) to keep your query readable.

Ready to practice conditional logic? Try our CASE WHEN exercises on SQiLs.

Cheat Sheet

Common Mistakes

1. Forgetting the ELSE Clause

Without ELSE, unmatched rows return NULL, which can silently break downstream calculations.

-- Bad: missing ELSE
CASE WHEN Total > 10 THEN 'High' END

-- Good: explicit fallback
CASE WHEN Total > 10 THEN 'High' ELSE 'Standard' END

2. Wrong Condition Order

CASE evaluates top-to-bottom and stops at the first match. Place the most specific conditions first.

-- Bad: every row matches the first condition
CASE WHEN Total > 0 THEN 'Low' WHEN Total > 10 THEN 'High' END

-- Good: check the higher threshold first
CASE WHEN Total > 10 THEN 'High' WHEN Total > 0 THEN 'Low' END

3. Mixing Data Types

Every THEN and ELSE branch must return the same data type. Mixing strings and numbers causes errors.

-- Bad: mixed types
CASE WHEN Total > 10 THEN 'High' ELSE 0 END

-- Good: consistent types
CASE WHEN Total > 10 THEN 'High' ELSE 'Low' END

4. Using CASE Where a Simple WHERE Works

Don't over-engineer. If you only need to filter rows, use WHERE instead of wrapping logic in CASE.

-- Overcomplicated
SELECT * FROM Invoice
WHERE CASE WHEN Total > 10 THEN 1 ELSE 0 END = 1;

-- Simpler
SELECT * FROM Invoice WHERE Total > 10;

Practice Exercises

Put your CASE WHEN skills to the test with these hands-on exercises from the Chinook database:

These exercises use the same Chinook database from this guide, so you can jump right in. If you need to brush up on JOINs before tackling exercise #5, we have you covered.

Summary

The SQL CASE WHEN expression is one of the most versatile tools in your query-writing toolkit:

• Simple CASE handles exact-match comparisons concisely.
• Searched CASE handles ranges, multi-column logic, and complex boolean conditions.
• Conditional aggregation (SUM/COUNT + CASE) pivots data in a single pass.
• CASE in ORDER BY gives you business-defined sort orders.
• Always include an ELSE clause and order conditions from most specific to least.

Master CASE WHEN and you unlock the ability to transform raw data into business-ready reports — no application code required.

Start practicing CASE WHEN on SQiLs

FAQs

What is the difference between CASE WHEN and IF in SQL?

CASE WHEN is part of the SQL standard and works in SELECT, ORDER BY, GROUP BY, and HAVING clauses. IF is a control-flow statement available in some databases (MySQL, SQL Server) but only inside stored procedures or limited contexts. For query-level conditional logic, always use CASE WHEN.

Can I use CASE WHEN with NULL values?

Yes, but use WHEN column IS NULL rather than WHEN column = NULL. In SQL, NULL = NULL evaluates to NULL (not TRUE), so equality checks against NULL never match.

Is CASE WHEN the same as a CASE statement?

Technically, CASE WHEN in a SELECT is a CASE expression (it returns a value). A "CASE statement" refers to control flow in procedural SQL (PL/pgSQL, T-SQL). In everyday conversation, people use both terms interchangeably, but understanding the distinction helps when reading documentation.

Does CASE WHEN affect query performance?

CASE WHEN in a SELECT clause has negligible performance impact — it simply evaluates conditions row by row. However, using CASE inside a WHERE or JOIN ON clause can prevent the optimizer from using indexes. When performance matters, prefer standard comparison operators in filter conditions.