Skip to main content
SQL Fundamentals
5 / 5
0%

CASE WHEN — conditional logic in SQL

0/3 solved3 to unlock next
Prev★ Final lesson

Lesson 05

CASE WHEN — conditional logic in SQL

Lesson Description

Use CASE WHEN to translate raw values into business-ready categories and labels. This lesson helps you express decision logic directly in SQL for cleaner reports and clearer analysis.

Easy Project

Mini project: Turn this lesson into a real-world query artifact by writing one clean business report query and validating output quality.

The problem CASE solves

So far every column you have produced either came straight from the table or was a single calculation applied the same way to every row. Real reports often need a rule that depends on the row. The store's merchandising team might ask: "Label every product as Budget, Standard or Premium based on its price." The hospital might want appointment statuses shown as friendly words on a patient portal. Neither label exists in the data. You have to derive it, row by row, from conditions.

CASE is SQL's if/else. It checks conditions in order and returns the value attached to the first one that is true. Because it is an expression, it can go anywhere a value can go: in SELECT, WHERE, ORDER BY, and inside aggregate functions. It is the last building block of this course, and it combines everything you have learned so far.

Syntax

There are two forms. The searched form takes any condition, just like a WHERE clause:

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

The simple form compares one expression against a list of values with =:

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

Rules that apply to both:

  • WHEN branches are checked top to bottom, and the first true branch wins. Later branches are not checked.
  • ELSE is optional. If no branch matches and there is no ELSE, the result is NULL.
  • Every CASE ends with END. Give the result an alias with AS, or PostgreSQL will name the column case.

Worked examples

Example 1: price bands with searched CASE

SELECT
  name,
  price,
  CASE
    WHEN price < 25  THEN 'Budget'
    WHEN price < 100 THEN 'Standard'
    ELSE 'Premium'
  END AS price_band
FROM products
ORDER BY price DESC;

All 27 products come back with a new price_band column. The 4K Monitor (349), Espresso Machine (299) and Wireless Headphones (199.99) are at the top, labeled Premium. Across the whole table you get 5 Budget, 16 Standard and 6 Premium products.

Look at the second branch: WHEN price < 100 does not say price >= 25. It does not need to, because any product under 25 already matched the first branch and stopped there. Ordering branches from the narrowest bucket to the widest lets you write short conditions. It also means the order is part of the logic, as you will see in Common mistakes.

Example 2: date ranges

Conditions can compare dates just as easily. Classify hospital doctors by when they joined:

SELECT
  first_name,
  last_name,
  hire_date,
  CASE
    WHEN hire_date < '2016-01-01' THEN 'Veteran'
    WHEN hire_date < '2020-01-01' THEN 'Established'
    ELSE 'Recent hire'
  END AS tenure_group
FROM doctors
ORDER BY hire_date;

This returns 15 doctors: 4 Veterans (Olivia Bennett through Sarah Johnson), 8 Established (Yuki Tanaka through Marcus Wright), and 3 Recent hires (Tomás García, Hassan Ali and Frank O'Brien, all 2020 or later).

Example 3: simple CASE for code translation

When you are mapping exact values to labels, the simple form reads more cleanly. Patient gender is stored as a single letter:

SELECT
  first_name,
  last_name,
  CASE gender
    WHEN 'M' THEN 'Male'
    WHEN 'F' THEN 'Female'
    ELSE 'Not recorded'
  END AS gender_label
FROM patients;

John Anderson shows Male, Mary Williams shows Female, and so on for all 30 patients. Every row in the sandbox has M or F, so the ELSE never fires here, but keeping it is good defensive practice: if a new code like 'X' arrives, you get a clear label instead of a silent NULL.

Example 4: no ELSE, and why NULL appears

Leave out ELSE when you only want to flag some rows. The purchasing team wants a stock alert:

SELECT
  name,
  stock,
  CASE
    WHEN stock < 20 THEN 'Reorder now'
    WHEN stock < 50 THEN 'Running low'
  END AS stock_alert
FROM products
ORDER BY stock;

The Espresso Machine (8), 4K Monitor (12) and Tennis Racket (18) say "Reorder now". Eight products between 20 and 49 units say "Running low". The remaining 16 products have 50 or more units, match no branch, and get NULL in stock_alert. That is often what you want, since a blank cell is easy to scan past. Just be aware of it before you filter or count on that column later.

Example 5: CASE in ORDER BY

Because CASE is an expression, you can sort by it. The hospital front desk wants upcoming work first: scheduled appointments, then no-shows to follow up, then cancellations, then completed visits.

SELECT id, appointment_date, status
FROM appointments
ORDER BY
  CASE status
    WHEN 'scheduled' THEN 1
    WHEN 'no-show'   THEN 2
    WHEN 'cancelled' THEN 3
    ELSE 4
  END,
  appointment_date;

All 66 appointments come back. The first 20 rows are the scheduled ones, starting with appointment 31 on 2025-04-08. Then the 2 no-shows, then the 1 cancellation, then the 43 completed visits. Alphabetical sorting could never produce this order; the CASE turns each status into a rank number, and ORDER BY sorts on that number. appointment_date breaks ties inside each group.

Bonus: CASE inside a count

You will meet aggregate functions in the Aggregations course, but here is a preview of a pattern you will use constantly. Put a CASE inside SUM to count rows that meet a condition:

SELECT
  SUM(CASE WHEN price >= 100 THEN 1 ELSE 0 END) AS premium_products,
  COUNT(*) FILTER (WHERE price < 25)            AS budget_products
FROM products;

One row comes back: 6 premium and 5 budget products. The FILTER clause is PostgreSQL's cleaner way to do the same thing.

Under the hood

  • CASE is evaluated once per row, at the point where it appears. In SELECT that is after WHERE has filtered the rows; in ORDER BY it is during sorting.
  • PostgreSQL stops at the first true WHEN. In general it also does not evaluate the THEN results of branches it did not pick, which is why CASE WHEN qty = 0 THEN NULL ELSE total / qty END is a common way to avoid division by zero.
  • All THEN and ELSE results must share one data type. PostgreSQL picks a common type from the branches, and if it cannot convert one of them, the query fails.
  • The simple form CASE x WHEN v is shorthand for x = v. Since NULL = NULL is unknown, not true, a simple CASE manager_id WHEN NULL THEN ... never matches. Use the searched form with WHEN manager_id IS NULL.

Common mistakes

Branches in the wrong order

CASE
  WHEN price < 100 THEN 'Standard'
  WHEN price < 25  THEN 'Budget'
  ELSE 'Premium'
END

This runs without error and gives 21 Standard, 6 Premium and 0 Budget products. A 12.50 jump rope is less than 100, so it matches the first branch and never reaches the second. Put narrower conditions first, or write each branch with both bounds (price >= 25 AND price < 100) so order no longer matters.

Mixing types across branches

SELECT name,
  CASE WHEN stock < 20 THEN 'Reorder' ELSE stock END
FROM products;

PostgreSQL fails with invalid input syntax for type integer: "Reorder". One branch is text and the other is an integer, and PostgreSQL tried to make everything an integer. Make both branches text, for example ELSE stock::text, or return the label in one column and the number in another.

Testing NULL with simple CASE

CASE manager_id WHEN NULL THEN 'Top of org' ELSE 'Reports to someone' END labels Steven King, who has no manager, as "Reports to someone". Switch to CASE WHEN manager_id IS NULL THEN 'Top of org' ELSE 'Reports to someone' END.

Forgetting the alias

Without AS, the output column is literally named case. That is confusing in a report and will fail any check that expects a specific column name, including the grader on this site.

How other databases differ

CASE is part of the SQL standard and works the same way in MySQL, SQL Server, SQLite and Oracle. Some databases add shortcuts: MySQL has IF(condition, a, b) and SQL Server has IIF(condition, a, b). PostgreSQL has neither, so CASE is the portable choice. The FILTER (WHERE ...) clause shown above is supported by PostgreSQL and SQLite but not by MySQL or SQL Server, where you use the SUM(CASE ...) pattern instead.

Practice

The exercises below this lesson ask you to label HR employees by salary tier, translate store order statuses into customer-friendly text, and group hospital patients by birth date. Each one names the exact labels and the output column to produce, so copy them carefully, and think about which branch has to come first.

Key takeaways

  • CASE returns the result of the first WHEN that is true, checked from top to bottom.
  • Without ELSE, unmatched rows get NULL.
  • Branch order is logic: put narrow conditions before broad ones.
  • Use the searched form for ranges and IS NULL; use the simple form for exact code-to-label mapping.
  • CASE is an expression, so it works in SELECT, WHERE, ORDER BY and inside aggregates. Always alias it.

CASE WHEN — conditional logic in SQL: in depth

CASE is SQL's if/else. It turns raw values into categories a reader understands (salary bands, order sizes, appointment outcomes) and is the building block for conditional counts and pivot-style reports.

Worked examples

Bucket salaries into bands

SELECT first_name, salary,
       CASE
         WHEN salary >= 15000 THEN 'High'
         WHEN salary >= 7000  THEN 'Mid'
         ELSE 'Entry'
       END AS salary_band
FROM employees;

Conditions are checked top to bottom and the first true one wins, so the order of WHEN clauses matters.

Conditional counts in one pass

SELECT COUNT(*) AS total,
       SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM appointments;

Each CASE turns a row into 1 or 0, and SUM adds them up, giving several counts from a single scan.

Common mistakes

  • Ordering WHEN clauses from broad to narrow. WHEN salary > 0 first would label everyone before the higher bands are checked.
  • Leaving out ELSE: unmatched rows get NULL, which then disappears from counts and labels.
  • Mixing result types (one branch returns text, another a number). PostgreSQL raises an error; cast every branch to the same type.

Other databases

  • PostgreSQL also supports COUNT(*) FILTER (WHERE status = 'completed'), a cleaner way to write conditional counts.
  • MySQL has IF(cond, a, b) and SQL Server has IIF(cond, a, b) as shorthands; CASE works everywhere.
Check yourself: A salary of 15000 with the CASE above: which band does it get, and why?

'High'. The first condition, salary >= 15000, is true, and CASE stops at the first match.

Concept check

Quick check: CASE WHEN — conditional logic in SQL

3 quick questions. One at a time. Instant score at the end.

Exercise 1

Employee salary tier

Enterprise Workforce AnalyticseasyPostgreSQL

Return first_name, last_name, salary, and a tier column: 'Junior' if salary < 5000, 'Mid' if salary < 10000, otherwise 'Senior'. Order by salary ascending.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

Human-readable order status

Commerce Operations IntelligenceeasyPostgreSQL

From the store sandbox, return id (alias order_id) and a status_label column that maps: 'shipped' → 'On the way', 'delivered' → 'Complete', 'cancelled' → 'Cancelled', anything else → 'Processing'. Order by id.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

Patient age group classification

Clinical Care PerformanceeasyPostgreSQL

From the hospital sandbox, return first_name, last_name, date_of_birth, and an age_group column: 'Born after 1990' if dob > 1990-01-01, 'Born 1960–1990' if dob > 1960-01-01, otherwise 'Born before 1960'. Order by date_of_birth DESC.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Final checkpoint

End-of-course high-level quiz

Pass mark: 80%. Review each explanation after you submit.

1. Which clause should you use to filter grouped results?

2. What is the safest way to avoid accidental row multiplication in joins?

3. Which feature is best for ranking rows within partitions?

4. Why should final analytical queries usually include ORDER BY?

5. When query logic gets complex, what improves readability most?

SQL Fundamentals
5 / 5
0%

CASE WHEN — conditional logic in SQL

0/3 solved3 to unlock next
Prev★ Final lesson