SQL CASE WHEN

WHEN checks a condition; THEN supplies the value to use if that condition is true — the core building block every CASE expression is made from.

What & Why

The full syntax is CASE WHEN condition THEN value END. Postgres checks condition for the current row; if it's true, the whole expression evaluates to value. The END keyword is mandatory — it's what tells Postgres the CASE expression is finished.

It helps to think of CASE WHEN ... THEN ... END as a single unit that behaves exactly like a column value once it's evaluated — you can give it a name with AS, just like any other column in SELECT.

See How It Works

BUSINESS QUESTION

Marketing wants to flag campaigns whose spend is at least 55,000, leaving the other rows as NULL.

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 CASE classify campaign spendExample 1 of 4

CASE WHEN spend >= 55000 THEN 'high spend' END

name	spend	spend_flag
Spring Launch	55000.00	high spend
Retention Webinar	45000.00	?
Finance Retargeting	50000.00	?
Enterprise Search	60000.00	?

Spring Launch meets the WHEN condition, so CASE returns the first branch.

EXAMPLE QUERY

SELECT
  name,
  spend,
  CASE WHEN spend >= 55000 THEN 'high spend' END AS spend_flag
FROM marketing.campaigns
ORDER BY id;

Now You Try

Practice this concept

Marketing wants to flag campaigns whose spend is at least 55,000, leaving the other rows as NULL.

Available schema

marketing

Prefix tables with marketing.table_name.

namespendspend_flag

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