CASE, COALESCE, and Conditional Logic

~10 min · queries, expressions

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

If/then/else inside SELECT

Sometimes you need conditional logic inside a query — categorising rows into buckets, replacing missing values, computing different totals based on type. CASE and COALESCE are the workhorses; learn both, plus their cousins NULLIF and GREATEST/LEAST.

The two CASE shapes

Searched CASE evaluates conditions in order: CASE WHEN x >= 100 THEN 'high' ... END. Simple CASE matches a single expression: CASE status WHEN 'P' THEN 'Pending' ... END. Searched is more flexible; simple is briefer when you're matching equality to one column.

Code

Searched CASE for bucketing·sql

SELECT id, total,
       CASE
         WHEN total >= 1000 THEN 'large'
         WHEN total >= 100  THEN 'medium'
         ELSE 'small'
       END AS order_size
FROM orders;

Simple CASE for code-to-label·sql

SELECT id,
       CASE status
         WHEN 'P' THEN 'Pending'
         WHEN 'S' THEN 'Shipped'
         WHEN 'D' THEN 'Delivered'
         WHEN 'R' THEN 'Returned'
         ELSE 'Unknown'
       END AS status_label
FROM orders;

COALESCE / NULLIF / GREATEST·sql

SELECT name,
       COALESCE(nickname, first_name, 'Anonymous') AS display_name,
       NULLIF(notes, '') AS notes,             -- treat empty string as NULL
       GREATEST(login_count, 0) AS safe_count, -- guard against negatives
       LEAST(quota, used)                       -- the smaller of two
FROM users;

External links

PostgreSQL Conditional Expressions

Exercise

Write a query that uses CASE in SELECT to bucket users by tenure ('new', 'regular', 'veteran') AND in ORDER BY to sort by a custom status priority. Use COALESCE on at least one column.