Conditional Expressions
beginneradvanced
2 min read
What are Conditional Expressions?
Conditional expressions let you apply if-then-else logic directly in SQL. PostgreSQL supports CASE, COALESCE, NULLIF, and GREATEST/LEAST.
Syntax
-- CASE WHEN
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END
-- COALESCE (first non-NULL value)
COALESCE(val1, val2, val3)
-- NULLIF (returns NULL if values are equal)
NULLIF(val1, val2)When to Use
- Categorizing or labeling rows based on conditions
- Providing default values for NULLs
- Avoiding division-by-zero errors
- Creating readable status columns
Key Points
- CASE — The most versatile. Works in SELECT, WHERE, ORDER BY, GROUP BY.
- COALESCE — Returns the first non-NULL argument. Great for defaults.
- NULLIF — Returns NULL if the two arguments are equal. Prevents divide-by-zero: x / NULLIF(y, 0).
- Short-Circuit — CASE stops at the first true condition.
- Simple CASE —
CASE col WHEN 'A' THEN 'Alpha'is shorthand for equality checks.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
Conditional Expressions Challenge
Write a query that solve this task: label students as Teen or Adult.
Expected result
Each student with a category label based on their age.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What are Conditional Expressions?
Initializing database...Each run starts from fresh sample data.
More Examples
COALESCE for defaults
Replace NULL grades with 'Not Graded'.
Initializing database...Each run starts from fresh sample data.
NULLIF to avoid division by zero
Safely divide total by count.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Can I use CASE in a WHERE clause?
Yes, but it is uncommon. Usually you can express the logic with AND/OR more clearly. CASE is most useful in SELECT and ORDER BY.
How many WHEN clauses can a CASE have?
There is no practical limit. However, too many WHEN clauses may indicate that a lookup table or JOIN would be cleaner.