Course contentsShow
← Data Science
Lesson 1031 of 2,14526. Advanced SQLFree lesson

Simple CASE vs Searched CASE

Understand the difference between simple CASE (equality checks) and searched CASE (complex conditions).

Simple CASE vs Searched CASE

You'll learn: The two flavors of CASE expressions—simple CASE for straightforward equality checks and searched CASE for complex logical conditions.

Understanding the Two CASE Styles

In SQL, CASE expressions come in two forms that solve different problems:

Simple CASE works like a switch statement in programming. You specify one column or expression once, then test it against different values. It's compact and readable when you're checking column = value1, column = value2, etc.

Searched CASE is more flexible—each WHEN clause can contain any boolean condition. Use this when you need comparison operators (>, <, !=), multiple columns, OR/AND logic, or anything beyond simple equality.

Simple CASE Syntax

CASE status
    WHEN 'active' THEN 'Active User'
    WHEN 'pending' THEN 'Awaiting Approval'
    WHEN 'suspended' THEN 'Account Suspended'
    ELSE 'Unknown'
END

This checks if status equals each value—clean and straightforward.

Searched CASE Syntax

CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age >= 18 AND age < 65 THEN 'Adult'
    WHEN age >= 65 THEN 'Senior'
    ELSE 'Unknown'
END

Notice there's no column after CASE—each WHEN is a complete boolean expression.

When to Choose Which

Use simple CASE when checking one column against specific values (equality only).

Use searched CASE when you need:

  • Range checks (age > 18)
  • Multiple conditions combined with AND/OR
  • Different columns in different conditions
  • Complex logic

Think of simple CASE as a shortcut for the specific scenario where searched CASE would be repetitive.

Key Takeaway: Simple CASE is elegant for equality checks on one expression; searched CASE handles any complex conditional logic you need.