Beginner7 min read PostgreSQL

NULL Handling — COALESCE, NULLIF & IS NULL

Understand why NULL breaks comparisons and arithmetic, and learn COALESCE, NULLIF, and IS NULL to write bulletproof queries.

NULL
COALESCE
NULLIF
IS NULL
data quality

Use this when you need to:

Wrap an aggregate in COALESCE so a missing value doesn't drop the row from a count or sum
Write filters that don't silently drop NULL rows — `WHERE col != X` skips NULLs, `IS DISTINCT FROM` doesn't
Detect data-quality issues — count rows with NULL in critical columns, find columns that are mostly NULL
Use NULLIF to treat sentinel values (empty string, 0, '-') as missing, or to prevent divide-by-zero

NULL in SQL means unknown or missing — it is not zero, not an empty string, not false. Any arithmetic or comparison involving NULL produces NULL, which causes rows to silently disappear from results.

NULL = NULL evaluates to NULL (unknown), not TRUE, and WHERE keeps only rows where the condition is TRUE, so the row is dropped. You must use IS NULL or IS NOT NULL to test for missing values. This is the #1 beginner trap.

IS NULL / IS NOT NULL

-- Find course sections with no instructor of record yet
SELECT course_sections_id, sec_short_title, sec_term
FROM ods_course_sections
WHERE sec_faculty_info IS NULL;

-- Find sections that have one assigned
SELECT course_sections_id, sec_short_title, sec_faculty_info
FROM ods_course_sections
WHERE sec_faculty_info IS NOT NULL;

COALESCE — First Non-NULL Value

COALESCE accepts multiple arguments and returns the first one that is not NULL. PostgreSQL evaluates arguments left to right and stops as soon as it finds a non-NULL value.

-- Most people have no middle name. first_name || ' ' || middle_name
-- would make the whole full name NULL for them, so fall back to ''
SELECT first_name || ' ' || COALESCE(middle_name || ' ', '') || last_name AS full_name
FROM ods_person;

-- Multi-level fallback: SAT if reported, else ACT, else a label.
-- 'SAT ' || NULL is NULL, so each argument falls through when its score is missing.
SELECT
    applications_id,
    COALESCE('SAT ' || appl_sat_total,
             'ACT ' || appl_act_composite,
             'No test score')          AS test_score
FROM ods_applications;

-- Replace NULL grade with a display label (Fall 2026 is still in progress)
SELECT
    scs_student,
    scs_course_name,
    COALESCE(scs_final_grade, 'In Progress') AS grade_display
FROM ods_stu_course_sec
WHERE scs_term = '2026FA';

NULLIF — Conditional NULL

NULLIF(a, b) returns NULL when a equals b, otherwise returns a. Its most common use is protecting against divide-by-zero errors, and treating empty strings the same as NULL.

-- Divide-by-zero protection: 13 aid records have zero calculated need,
-- and dividing by them raises "division by zero" and kills the whole query
SELECT
    fa_student,
    fa_year,
    ROUND(fa_total_grants / NULLIF(fa_total_need, 0), 2) AS share_of_need_met_by_grants
FROM ods_financial_aid;

-- Treat an empty or blank string as NULL so COALESCE can skip it
SELECT
    first_name,
    COALESCE(NULLIF(TRIM(middle_name), ''), '(none)') AS middle_name
FROM ods_person;

NULLIF(a, b) is exactly equivalent to CASE WHEN a = b THEN NULL ELSE a END — just shorter.

NULL in Aggregate Functions

-- COUNT(*) counts every row; COUNT(column) skips NULLs
SELECT
    COUNT(*)               AS total_rows,       -- 1980
    COUNT(scs_final_grade) AS rows_with_grade   -- 1536: NULLs not counted
FROM ods_stu_course_sec;

-- AVG() automatically ignores NULLs: the 29 applications with no
-- high-school GPA are left out, not averaged in as zero
SELECT AVG(appl_hs_gpa) FROM ods_applications;

Practice challenge

In ods_stu_course_sec, scs_final_grade is NULL while a course is still in progress. Write a query returning scs_student, the course title (crs_title, on ods_courses), and grade_display showing the actual grade or 'In Progress' when NULL. Filter to the 2026SP and 2026FA terms: Spring's grades are posted, Fall is still in progress.

Solution

SELECT
    scs.scs_student,
    c.crs_title,
    COALESCE(scs.scs_final_grade, 'In Progress') AS grade_display
FROM ods_stu_course_sec scs
LEFT JOIN ods_course_sections cs ON cs.course_sections_id = scs.scs_course_section
LEFT JOIN ods_courses c          ON c.courses_id          = cs.sec_course
WHERE scs.scs_term IN ('2026SP', '2026FA')
ORDER BY scs.scs_student, c.crs_title;

Practise this on real university data

Write real PostgreSQL in your browser and get it graded on the rows it returns. No signup and nothing to install for your first query.

Run your first query Browse practice problems

See all 21 lessons