SQL CASE Statement: Complete Guide with Interactive Examples

The SQL CASE statement is your tool for adding conditional logic to queries. It lets you transform data, create categories, and build dynamic output based on conditions—all within your SQL. Whether you’re building reports, cleaning data, or creating derived columns, CASE is essential.

Throughout this guide, you’ll find interactive SQL exercises that let you practice CASE statements in your browser—no database setup required. Write queries, get instant feedback, and see if your results match the expected output.

What is the CASE Statement?

CASE is SQL’s way of implementing if-then-else logic. It evaluates conditions and returns different values based on which condition is true. Think of it as a switch statement that works directly in your queries.

There are two forms of the CASE statement:

Searched CASE (most common):

CASE 
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

Simple CASE (for exact matches):

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ELSE default_result
END

Basic CASE: Creating Categories

The most common use of CASE is transforming values into categories. Imagine you have a sales table and want to label orders by size:

SELECT 
  order_id,
  amount,
  CASE 
    WHEN amount < 100 THEN 'Small'
    WHEN amount < 500 THEN 'Medium'
    ELSE 'Large'
  END as order_size
FROM orders;

This query evaluates each row:

1. If amount is under 100, it returns ‘Small’
2. If amount is under 500 (but not under 100), it returns ‘Medium’
3. Otherwise, it returns ‘Large’

The ELSE clause is optional but recommended. Without it, unmatched conditions return NULL.

Try it yourself:

[[ error ]]

[[ testData.solution ]]

Simple CASE for Exact Value Matching

When you’re matching against specific values rather than ranges, the simple CASE syntax is cleaner. It compares one expression against multiple possible values:

SELECT 
  product_id,
  status,
  CASE status
    WHEN 'A' THEN 'Active'
    WHEN 'D' THEN 'Discontinued'
    WHEN 'P' THEN 'Pending'
    ELSE 'Unknown'
  END as status_label
FROM products;

This is equivalent to writing:

CASE 
  WHEN status = 'A' THEN 'Active'
  WHEN status = 'D' THEN 'Discontinued'
  -- etc.
END

Use simple CASE when you’re doing equality comparisons against a single column or expression. Use searched CASE when you need more complex conditions.

Try it yourself:

[[ error ]]

[[ testData.solution ]]

CASE Inside Aggregate Functions

One powerful pattern is using CASE inside aggregate functions to count or sum conditionally. This is sometimes called “conditional aggregation”:

SELECT 
  COUNT(*) as total_customers,
  COUNT(CASE WHEN status = 'active' THEN 1 END) as active_customers,
  SUM(CASE WHEN country = 'USA' THEN revenue ELSE 0 END) as usa_revenue
FROM customers;

How it works:

• COUNT(CASE WHEN ... THEN 1 END) only counts rows where the condition is true (NULL values are ignored by COUNT)
• SUM(CASE WHEN ... THEN value ELSE 0 END) adds values only for matching rows

This technique is incredibly useful for creating summary reports with multiple breakdowns in a single query.

Try it yourself:

[[ error ]]

[[ testData.solution ]]

Handling NULL Values with CASE

NULL requires special attention in CASE statements. You can’t compare NULL using =—you must use IS NULL or IS NOT NULL:

SELECT 
  customer_name,
  CASE 
    WHEN email IS NOT NULL THEN email
    WHEN phone IS NOT NULL THEN phone
    ELSE 'No contact info'
  END as primary_contact
FROM customers;

Common patterns for NULL handling:

CASE 
  WHEN value IS NULL THEN 'Missing'
  WHEN value = '' THEN 'Empty'
  ELSE value
END

For simple NULL replacement, COALESCE is often cleaner than CASE. But CASE gives you more flexibility when you need to handle multiple conditions.

Try it yourself:

[[ error ]]

[[ testData.solution ]]

CASE in ORDER BY

You can use CASE to create custom sort orders:

SELECT product_name, category
FROM products
ORDER BY 
  CASE category
    WHEN 'Featured' THEN 1
    WHEN 'New Arrival' THEN 2
    WHEN 'Sale' THEN 3
    ELSE 4
  END,
  product_name;

This sorts products with ‘Featured’ first, then ‘New Arrival’, then ‘Sale’, with everything else at the end. Within each category, products are sorted by name.

CASE in WHERE Clauses

While you can use CASE in WHERE clauses, it’s often not the best approach:

SELECT * FROM orders
WHERE 
  CASE 
    WHEN @include_cancelled = 1 THEN 1
    ELSE status != 'cancelled'
  END = 1;

Usually, using OR/AND logic is clearer and more performant:

SELECT * FROM orders
WHERE @include_cancelled = 1 OR status != 'cancelled';

Nested CASE Statements

CASE statements can be nested for complex logic:

SELECT 
  product_name,
  CASE category
    WHEN 'Electronics' THEN
      CASE 
        WHEN price > 1000 THEN 'Premium Electronics'
        ELSE 'Standard Electronics'
      END
    WHEN 'Clothing' THEN
      CASE 
        WHEN price > 200 THEN 'Designer Clothing'
        ELSE 'Regular Clothing'
      END
    ELSE 'Other Products'
  END as product_class
FROM products;

While nested CASE works, deeply nested statements become hard to read. Consider restructuring your query or using a lookup table if nesting gets too deep.

Performance Considerations

CASE statements are generally efficient, but keep these tips in mind:

Evaluate conditions in order: SQL evaluates WHEN clauses from top to bottom and stops at the first match. Put the most common conditions first:

CASE 
  WHEN status = 'active' THEN 'Active'      -- Most common, check first
  WHEN status = 'pending' THEN 'Pending'    -- Less common
  WHEN status = 'archived' THEN 'Archived'  -- Rare
  ELSE 'Unknown'
END

Avoid expensive operations in CASE: Each condition might be evaluated, so avoid subqueries or complex calculations inside CASE when possible.

Index usage: CASE statements in WHERE clauses can prevent index usage. If performance matters, restructure to use simple conditions that can leverage indexes.

Real-World Examples

Status Display

Transform database codes into user-friendly labels:

SELECT 
  order_id,
  CASE status
    WHEN 'P' THEN 'Pending'
    WHEN 'S' THEN 'Shipped'
    WHEN 'D' THEN 'Delivered'
    WHEN 'C' THEN 'Cancelled'
    WHEN 'R' THEN 'Returned'
    ELSE 'Unknown'
  END as status_display
FROM orders;

Dynamic Calculations

Apply different discount rates based on customer tier:

SELECT 
  customer_name,
  order_total,
  CASE tier
    WHEN 'gold' THEN order_total * 0.15
    WHEN 'silver' THEN order_total * 0.10
    WHEN 'bronze' THEN order_total * 0.05
    ELSE 0
  END as discount_amount
FROM customers c
JOIN orders o ON c.id = o.customer_id;

Pivot-Style Reports

Create columnar summaries from row data:

SELECT 
  product_category,
  SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 1 THEN amount ELSE 0 END) as jan_sales,
  SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 2 THEN amount ELSE 0 END) as feb_sales,
  SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 3 THEN amount ELSE 0 END) as mar_sales
FROM sales
GROUP BY product_category;

CASE vs COALESCE vs NULLIF

Choose the right tool for the job:

Example comparison:

COALESCE(nickname, name)

CASE 
  WHEN nickname IS NOT NULL THEN nickname
  ELSE name
END

Both do the same thing, but COALESCE is cleaner for this use case.

Working with CASE in Beekeeper Studio

Writing complex CASE statements is easier with the right tools. Beekeeper Studio provides:

• Syntax highlighting: Makes CASE/WHEN/THEN/ELSE keywords easy to spot
• Query formatting: Automatically formats nested CASE statements for readability
• Autocomplete: Suggests column names as you type conditions
• Result comparison: Clearly shows transformed values alongside original data
• Multi-database support: CASE syntax works across PostgreSQL, MySQL, SQLite, SQL Server, and more

The free version includes everything you need to write and test complex CASE expressions effectively.

Key Takeaways

The SQL CASE statement is essential for conditional logic in your queries:

• Two forms exist: Searched CASE (with conditions) and simple CASE (for exact matches)
• ELSE is optional but recommended to handle unmatched conditions
• Conditions evaluate top-to-bottom—first match wins
• NULL requires special handling with IS NULL / IS NOT NULL
• Works in SELECT, ORDER BY, WHERE, and inside aggregate functions
• Conditional aggregation (CASE inside COUNT/SUM) is powerful for reports
• Keep nesting minimal for maintainable code
• Use COALESCE for simple NULL handling instead of CASE

Master CASE statements, and you’ll unlock the ability to transform, categorize, and analyze data directly in your SQL queries without needing post-processing in application code.

Ready to practice more? Try the interactive examples above, or explore other SQL tutorials to continue building your database skills.