SQL Multiple WHEN Conditions

Chain several WHEN branches together — Postgres checks them top to bottom and stops at the very first one that matches, even if a later condition would also have matched.

What & Why

A single WHEN only handles one condition. Real classification usually needs several tiers — "Low spend," "Medium spend," "High spend," and so on — which means stacking multiple WHEN ... THEN ... pairs inside one CASE: CASE WHEN cond1 THEN val1 WHEN cond2 THEN val2 ... END.

The genuinely important thing to understand here — more important than the syntax itself — is the evaluation order. Postgres checks each WHEN from top to bottom, in the exact order they're written, and stops the instant it finds one that's true. Every WHEN after that point is never even evaluated for that row, regardless of whether it would also have matched. This means the order you write your conditions in is not just a style choice — it can silently change your results if two conditions overlap.

See How It Works

BUSINESS QUESTION

Marketing compares the correct narrow-first campaign spend tiers with the same overlapping conditions written in the wrong order.

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

Watch the first matching WHEN branch winExample 1 of 4

specific-first CASE compared with broad-first CASE

id	name	spend	specific_first	broad_first
1	Spring Launch	55000.00	priority	standard
2	Retention Webinar	45000.00	?	?
3	Finance Retargeting	50000.00	?	?
4	Enterprise Search	60000.00	?	?

55,000 satisfies both thresholds: narrow-first returns priority, while broad-first stops too early at standard.

EXAMPLE QUERY

SELECT
  id,
  name,
  spend,
  CASE
    WHEN spend >= 55000 THEN 'priority'
    WHEN spend >= 50000 THEN 'standard'
    ELSE 'starter'
  END AS specific_first,
  CASE
    WHEN spend >= 50000 THEN 'standard'
    WHEN spend >= 55000 THEN 'priority'
    ELSE 'starter'
  END AS broad_first
FROM marketing.campaigns
ORDER BY id;

Now You Try

Practice this concept

Marketing compares narrow-first and broad-first CASE branches over the same overlapping spend thresholds.

Available schema

marketing

Prefix tables with marketing.table_name.

idnamespendspecific_firstbroad_first

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