SQL Nested CASE

A full CASE expression can live inside a THEN or ELSE branch of another CASE — useful for genuinely two-level classification.

What & Why

Since a CASE expression evaluates to an ordinary value, nothing stops another CASE expression from being used as that value. This lets one condition decide broadly, and a second, nested condition refine the answer further — genuinely useful when a classification has two independent dimensions rather than one flat list of categories.

See How It Works

BUSINESS QUESTION

Marketing wants active campaigns split into high- and standard-spend labels, while every inactive campaign keeps one fallback label.

id	name	channel	spend	start_date	end_date	status	target_segment
1	Spring Launch	google_ads	55000.00	2024-01-15	2024-03-31	active	smb
2	Retention Webinar	email	45000.00	2024-02-10	2024-04-15	active	enterprise
3	Finance Retargeting	linkedin	50000.00	2024-03-12	2024-05-31	active	enterprise
4	Enterprise Search	google_ads	60000.00	2024-04-01	2024-06-30	active	enterprise

EXAMPLE QUERY

SELECT
  id AS campaign_id,
  name,
  CASE
    WHEN status = 'active' THEN
      CASE
        WHEN spend >= 10000 THEN 'active — high spend'
        ELSE 'active — standard spend'
      END
    ELSE 'not active'
  END AS campaign_label
FROM marketing.campaigns
ORDER BY campaign_id;

RESULT — exact output from the displayed Queryflo rows

campaign_id	name	campaign_label
1	Spring Launch	active — high spend
2	Retention Webinar	active — high spend
3	Finance Retargeting	active — high spend
4	Enterprise Search	active — high spend

All displayed campaigns are active and have spend above the nested threshold.

Now You Try

Practice this concept

Marketing wants active campaigns split into high- and standard-spend labels while inactive campaigns share one fallback label.

Available schema

marketing

Prefix tables with marketing.table_name.

campaign_idnamecampaign_label

marketing.campaigns

Column	Type
id	integer
name	text
channel	text
spend	numeric
start_date	date
end_date	date
status	text
target_segment	text
legacy_id	text

query.sql

Intermediate business practice