NULL Semantics in PostgreSQL

NULL means "unknown" or "absent" — not zero, not an empty string. This one distinction explains almost every NULL-related surprise in SQL.

Three-Valued Logic

Every comparison involving NULL returns NULL (neither true nor false) — not false.

SELECT NULL = NULL;      -- NULL (not true!)
SELECT NULL <> NULL;     -- NULL
SELECT 5 > NULL;         -- NULL
SELECT NULL IS NULL;     -- true  -- IS is the only way to test for NULL

A WHERE clause only keeps rows where the condition is true — rows where it evaluates to NULL are silently dropped, which is why WHERE column = NULL never matches anything (see the Filtering Operators lesson).

COALESCE — First Non-NULL Value

Returns the first argument that isn't NULL, checking left to right — perfect for supplying a fallback/default.

SELECT name, COALESCE(phone, 'No phone on file') AS phone
FROM employees;

SELECT COALESCE(nickname, first_name, 'Unknown') AS display_name
FROM users;

NULLIF — Turn a Specific Value Into NULL

Returns NULL if the two arguments are equal, otherwise returns the first argument — useful for avoiding division-by-zero errors.

SELECT revenue / NULLIF(units_sold, 0) AS revenue_per_unit
FROM sales;
-- if units_sold is 0, NULLIF turns it into NULL, and dividing by NULL gives NULL instead of an error

NULLs in Aggregates

Aggregate functions ignore NULL values entirely, except COUNT(*).

SELECT COUNT(*) FROM employees;          -- counts ALL rows, including NULL phone numbers
SELECT COUNT(phone) FROM employees;      -- counts only rows where phone IS NOT NULL
SELECT AVG(salary) FROM employees;       -- NULL salaries are excluded from the average, not treated as 0

NULLs in Joins

A LEFT JOIN (see the Joins lesson) fills in NULL for every column of the right table when there's no match — always check for this explicitly when it matters:

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;
-- employees with NO department assigned at all

Common Errors & Troubleshooting

• WHERE column = NULL always returning zero rows — this is the single most common NULL mistake; use IS NULL instead — = can never evaluate to true against NULL.
• NULLIF not preventing a divide-by-zero error where expected — double check both operands' types match; NULLIF(units_sold, 0) only converts to NULL if units_sold is exactly 0, not some other falsy-looking value.
• Assuming COUNT(column) equals COUNT(*) — they differ whenever the column contains any NULL values — COUNT(*) counts every row, COUNT(column) only counts rows where that column is non-NULL.

Key Takeaways

• NULL represents "unknown" — comparisons against it produce NULL, not true/false, so WHERE silently excludes those rows.
• COALESCE supplies a fallback value; NULLIF converts a specific value into NULL (classic use: avoiding divide-by-zero).
• Aggregate functions skip NULL values automatically — AVG() divides by the count of non-NULL rows, not the total row count.