SQL COALESCE and NULLIF
intermediateadvanced
2 min read
What are COALESCE and NULLIF?
COALESCE(a, b, c, ...)returns the first non-NULL argument.NULLIF(a, b)returns NULL ifaequalsb; otherwise returnsa.
Syntax
SELECT COALESCE(column, 'default_value') FROM table_name;
SELECT NULLIF(column, 0) FROM table_name;When to Use
- Replacing NULL with a readable default in output
- Avoiding division by zero:
value / NULLIF(divisor, 0) - Providing fallback values in a chain
Key Points
- COALESCE Chain —
COALESCE(a, b, c)checks a, then b, then c. - Standard SQL — COALESCE is a standard SQL expression, not PostgreSQL-specific.
- Division Safety —
total / NULLIF(count, 0)returns NULL instead of a division error. - NULLIF Use Case — Convert empty strings or placeholder values to NULL.
- Performance — Both are lightweight expressions with no performance concerns.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
SQL COALESCE and NULLIF Challenge
Write a query that solve this task: show a default text when the email is NULL.
Expected result
Students with NULL emails now show "No email provided".
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What are COALESCE and NULLIF?
Initializing database...Each run starts from fresh sample data.
More Examples
Safe division with NULLIF
Calculate average order value without dividing by zero.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
What is the difference between COALESCE and IFNULL?
COALESCE is standard SQL and accepts multiple arguments. IFNULL is MySQL-specific with exactly two arguments. In PostgreSQL, use COALESCE.
When would I use NULLIF?
The most common use case is preventing division by zero: dividing by NULLIF(value, 0) returns NULL instead of raising an error.