IS NULL and COALESCE

Learning Focus

Use this lesson to handle NULL values in conditions — a common source of hard-to-debug query errors.

IS NULL / IS NOT NULL

-- Find rows where a column is NULL
SELECT * FROM employees WHERE manager_id IS NULL;

-- Find rows where a column is NOT NULL
SELECT * FROM employees WHERE manager_id IS NOT NULL;

COALESCE in WHERE Conditions

-- Use COALESCE to provide a default before comparison
SELECT * FROM employees
WHERE COALESCE(team_name, 'Unassigned') = 'Unassigned';

-- Replace NULL in a computed column
SELECT
    name,
    COALESCE(bonus, 0) + salary AS total_compensation
FROM employees;

NULLIF in Filters

-- Avoid division by zero
SELECT
    name,
    revenue / NULLIF(transactions, 0) AS avg_order_value
FROM sales_data;

IS DISTINCT FROM (NULL-safe comparison)

-- Returns true even when comparing NULL to non-NULL
SELECT * FROM employees WHERE manager_id IS DISTINCT FROM 5;

-- True even when both are NULL
SELECT * FROM employees WHERE note IS NOT DISTINCT FROM NULL;

Practical NULL Patterns

-- Count rows with missing data
SELECT COUNT(*) FROM employees WHERE hire_date IS NULL;

-- Fill missing manager with 'No Manager'
SELECT name, COALESCE(manager_name, 'No Manager') AS manager
FROM employees;

-- Flag rows with any NULL in key columns
SELECT *,
    CASE WHEN hire_date IS NULL OR department IS NULL THEN true ELSE false END AS has_missing
FROM employees;

Common Pitfalls

Pitfall	Consequence	Prevention
WHERE col = NULL	Always 0 rows	Use IS NULL
NOT considering NULLs in OR conditions	Unexpected empty results	Test with IS NULL checks
COALESCE with type mismatch	Type error	Ensure all arguments share compatible types

Quick Reference

WHERE col IS NULL
WHERE col IS NOT NULL
COALESCE(col, default)
NULLIF(col, 0)
WHERE col IS DISTINCT FROM val
WHERE col IS NOT DISTINCT FROM NULL

What's Next

• Next: Aggregate Functions - Summarize data with COUNT, SUM, AVG, and more.
• Module Overview - Return to this module index.