0Pricing
SQL Academy · Lesson

Searched vs Simple CASE

Two ways to write CASE.

Searched vs Simple CASE is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Two Ways to Write CASE

SQL gives you two flavors of the CASE expression: the Simple CASE and the Searched CASE. Both return a value based on conditions, but they differ in how those conditions are written.

By the end of this lesson you will know when to reach for each form and how to avoid common mistakes when mixing them up.

Simple CASE — The Idea

A Simple CASE compares one expression to a list of values. Think of it like a lookup table: you pick one column or value on the left, then list what that value equals in each WHEN branch.

Syntax: CASE expression WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE default END

SELECT
  product_name,
  category_id,
  CASE category_id
    WHEN 1 THEN 'Electronics'
    WHEN 2 THEN 'Clothing'
    WHEN 3 THEN 'Books'
    ELSE 'Other'
  END AS category_name
FROM products;

Simple CASE in Practice

Here the CASE expression sits right after the keyword and the column status is compared to each WHEN value using equality. Notice that you do not write status = in the WHEN branches — the comparison is implied.

SELECT
  order_id,
  status,
  CASE status
    WHEN 'pending'   THEN 'Awaiting payment'
    WHEN 'paid'      THEN 'Payment received'
    WHEN 'shipped'   THEN 'On its way'
    WHEN 'delivered' THEN 'Completed'
    ELSE 'Unknown status'
  END AS status_label
FROM orders;

Searched CASE — The Idea

A Searched CASE has no expression right after the CASE keyword. Instead, each WHEN branch holds a full Boolean condition. This makes it far more flexible: you can mix different columns, use range checks, or call functions inside each WHEN.

Syntax: CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default END

SELECT
  employee_name,
  salary,
  CASE
    WHEN salary < 30000  THEN 'Entry Level'
    WHEN salary < 60000  THEN 'Mid Level'
    WHEN salary < 100000 THEN 'Senior'
    ELSE 'Executive'
  END AS salary_band
FROM employees;

Searched CASE in Practice

Because each WHEN accepts any Boolean expression, you can combine multiple columns in one branch. The database evaluates WHEN clauses top-to-bottom and returns the result of the first branch that is TRUE.

SELECT
  product_name,
  stock,
  price,
  CASE
    WHEN stock = 0              THEN 'Out of stock'
    WHEN stock < 10 AND price > 100 THEN 'Low stock — premium item'
    WHEN stock < 10             THEN 'Low stock'
    ELSE 'In stock'
  END AS availability
FROM products;

Key Structural Difference

The table below captures the core distinction:

Simple CASE — one expression after CASE, each WHEN checks equality against that expression.
Searched CASE — no expression after CASE, each WHEN is a standalone Boolean condition.

Because Simple CASE only does equality checks, it cannot handle ranges, NULLs, or multi-column conditions. For anything beyond a direct value match, use Searched CASE.

-- Simple CASE: equality only
SELECT CASE day_number
         WHEN 1 THEN 'Monday'
         WHEN 2 THEN 'Tuesday'
         ELSE 'Other'
       END AS day_name
FROM schedule;

-- Searched CASE: any condition
SELECT CASE
         WHEN day_number BETWEEN 1 AND 5 THEN 'Weekday'
         WHEN day_number IN (6, 7)        THEN 'Weekend'
       END AS day_type
FROM schedule;

NULL Handling — Why Searched CASE Wins

A Simple CASE uses = internally, and NULL = NULL is never TRUE in SQL. So a Simple CASE cannot match a NULL column. A Searched CASE lets you write WHEN column IS NULL THEN ... which works correctly.

-- Simple CASE: NULL branch never matches
SELECT CASE manager_id
         WHEN NULL THEN 'No manager'   -- this will NEVER fire
         ELSE 'Has manager'
       END
FROM employees;

-- Searched CASE: correct NULL check
SELECT CASE
         WHEN manager_id IS NULL THEN 'No manager'
         ELSE 'Has manager'
       END AS manager_status
FROM employees;

Mixing Columns in Searched CASE

A major strength of Searched CASE is that each WHEN branch can reference completely different columns or call functions. This lets you express complex business rules that a Simple CASE cannot handle at all.

SELECT
  customer_name,
  total_orders,
  total_spent,
  CASE
    WHEN total_spent > 10000 AND total_orders > 20 THEN 'VIP'
    WHEN total_spent > 5000                        THEN 'Gold'
    WHEN total_orders > 10                         THEN 'Silver'
    ELSE 'Standard'
  END AS tier
FROM customers;

Using CASE in ORDER BY

Both forms of CASE can appear inside ORDER BY to create a custom sort order that does not exist in the data. Here a Simple CASE maps priority names to numbers so that rows sort in business priority order, not alphabetically.

SELECT ticket_id, subject, priority
FROM support_tickets
ORDER BY
  CASE priority
    WHEN 'critical' THEN 1
    WHEN 'high'     THEN 2
    WHEN 'medium'   THEN 3
    WHEN 'low'      THEN 4
    ELSE 5
  END ASC;

CASE Inside Aggregate Functions

Wrapping a Searched CASE inside SUM or COUNT is a classic pattern for conditional aggregation — sometimes called a pivot. It lets you produce multiple category totals in one scan instead of running several queries.

SELECT
  department,
  COUNT(*) AS total_employees,
  SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count,
  SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,
  SUM(CASE WHEN salary > 70000 THEN salary ELSE 0 END) AS high_earner_payroll
FROM employees
GROUP BY department;

Choosing the Right Form

A practical rule of thumb:

Use Simple CASE when you are mapping a single column to a fixed set of known values — it is shorter and easier to read.
Use Searched CASE for everything else: ranges, NULL checks, multi-column logic, or function calls inside conditions.

When in doubt, Searched CASE always works. You can even rewrite any Simple CASE as a Searched CASE — but not the other way around.

-- These two queries return identical results

-- Simple CASE (concise for equality)
SELECT CASE region_code
         WHEN 'US' THEN 'United States'
         WHEN 'UK' THEN 'United Kingdom'
         ELSE 'International'
       END AS region_name
FROM sales;

-- Equivalent Searched CASE
SELECT CASE
         WHEN region_code = 'US' THEN 'United States'
         WHEN region_code = 'UK' THEN 'United Kingdom'
         ELSE 'International'
       END AS region_name
FROM sales;

Quick Check

Test your understanding of Simple vs Searched CASE.

Lesson Recap

Here is what you learned in this lesson:

Simple CASE — place one expression after CASE; each WHEN checks whether that expression equals a specific value. Best for mapping a single column to a fixed list of labels.

Searched CASE — no expression after CASE; each WHEN holds a full Boolean condition. Supports ranges, NULL checks, multi-column logic, and function calls.

Both forms evaluate branches top-to-bottom and return the result of the first TRUE branch. When no branch matches and there is no ELSE, the result is NULL. For maximum flexibility, Searched CASE is always the safe choice — it can do everything Simple CASE can do and more.

Frequently asked questions

Is the “Searched vs Simple CASE” lesson free?

Yes — the full text of “Searched vs Simple CASE” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Searched vs Simple CASE”?

Two ways to write CASE. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Searched vs Simple CASE” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. The CASE Expression
  2. Searched vs Simple CASE
  3. Bucketing and Labeling Data
  4. CASE in ORDER BY and Aggregates
← Back to SQL Academy