How to Use CASE in SQL

Use CASE when SQL results need conditional logic inside a query. It works like if / else if / else, making it perfect for labels, status buckets, scoring bands, and custom sort logic.

What you’ll build or solve

You’ll learn how to use CASE in SQL inside SELECT, ORDER BY, and aggregate logic. You’ll also know how to keep conditions readable.

When this approach works best

This approach is the right choice when output values depend on conditions.

Common real-world scenarios include:

• Revenue tiers
• Pass or fail labels
• Order status groups
• Priority sorting
• Data cleanup mappings

This is a bad idea when the logic becomes large enough to deserve a lookup table.

Prerequisites

You only need:

• Basic SELECT and WHERE knowledge
• Familiarity with boolean conditions

Step-by-step instructions

Step 1: Write conditional branches with CASE

The basic structure is:

CASE
  WHEN condition THEN result
  WHEN condition THEN result
  ELSE fallback
END

Example:

SELECT order_id,
       CASE
         WHEN total >= 100 THEN 'high'
         WHEN total >= 50 THEN 'medium'
         ELSE 'low'
       END AS order_tier
FROM orders;

This creates a readable label per row.

It also works inside aggregates.

SELECT SUM(
  CASE
    WHEN status = 'paid' THEN total
    ELSE 0
  END
) AS paid_revenue
FROM orders;

What to look for:

• Conditions run top to bottom
• First match wins
• ELSE provides a fallback
• Great for labels and custom metrics
• Keep branch logic simple

Examples you can copy

Pass or fail

SELECT student_id,
       CASE
         WHEN score >= 60 THEN 'pass'
         ELSE 'fail'
       END
FROM exams;

Revenue bucket

CASE
  WHEN total > 1000 THEN 'enterprise'
  ELSE 'standard'
END

Paid revenue only

SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END)

Common mistakes and how to fix them

Mistake 1: Wrong branch order

What the reader might do:

WHEN total >= 50 THEN 'medium'
WHEN total >= 100 THEN 'high'

Why it breaks: 100 already matches the first condition.

Corrected approach:

Place stricter conditions first.

Mistake 2: Forgetting ELSE

What the reader might do:

CASE
  WHEN score >= 60 THEN 'pass'
END

Why it breaks: unmatched rows become NULL.

Corrected approach:

Always add a fallback when needed.

Mistake 3: Overloading business logic into SQL

What the reader might do:

Create 30+ WHEN branches.

Why it breaks: the query becomes hard to maintain.

Corrected approach:

Move stable mappings into a reference table.

Troubleshooting

If values land in the wrong bucket, check branch order.

If NULL appears unexpectedly, add ELSE.

If the query becomes too long, use a lookup table.

If repeated logic appears in many places, consider a view.

Quick recap

• Use CASE for SQL conditional logic
• First matching branch wins
• Order conditions from most specific to least
• Add ELSE for fallback
• Use lookup tables for large mappings