Nested CASE expressions in SQL

Introduction

Nested CASE expressions help you make decisions inside other decisions in your data queries. This lets you check multiple conditions step by step.

You want to assign categories based on multiple levels of conditions.

You need to check one condition, and inside it check another condition.

You want to create detailed labels or groups from your data.

You want to replace complex IF-ELSE logic in your query with readable SQL.

You want to handle special cases differently inside a general case.

Syntax

SQL

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

You can put one CASE expression inside another CASE expression.

Each CASE must end with END to close it.

Examples

This example assigns grades. If score is 90 or more, grade is A. If score is between 80 and 89, it checks again: 85 or more is B+, else B. Otherwise, grade is C.

SQL

SELECT
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN
      CASE
        WHEN score >= 85 THEN 'B+'
        ELSE 'B'
      END
    ELSE 'C'
  END AS grade
FROM students;

Here, products in the 'Fruit' category get a price label based on price. Others get 'Other'.

SQL

SELECT
  product,
  CASE
    WHEN category = 'Fruit' THEN
      CASE
        WHEN price < 1 THEN 'Cheap Fruit'
        ELSE 'Expensive Fruit'
      END
    ELSE 'Other'
  END AS price_label
FROM products;

Sample Program

This query labels sales by region and amount. It first checks the region, then inside that, checks if the amount is high or low.

SQL

WITH sales AS (
  SELECT 'North' AS region, 120 AS amount UNION ALL
  SELECT 'North', 80 UNION ALL
  SELECT 'South', 50 UNION ALL
  SELECT 'South', 200
)
SELECT
  region,
  amount,
  CASE
    WHEN region = 'North' THEN
      CASE
        WHEN amount > 100 THEN 'High North Sale'
        ELSE 'Low North Sale'
      END
    WHEN region = 'South' THEN
      CASE
        WHEN amount > 100 THEN 'High South Sale'
        ELSE 'Low South Sale'
      END
    ELSE 'Other Region'
  END AS sale_category
FROM sales;

Important Notes

Always close each CASE with END to avoid errors.

Nested CASE expressions can make queries longer but help organize complex logic clearly.

Indent your CASE statements to keep the code easy to read.

Summary

Nested CASE expressions let you check conditions inside other conditions.

They help create detailed categories or labels in your data.

Remember to close each CASE with END and keep your code readable.