SQLRepGuidesPlaygroundLearning path

NULL in SQL: IS NULL, COALESCE and the mistakes NULL causes

NULL means 'unknown'. Any comparison with it is unknown too, not true or false, so NULLs quietly drop out of filters unless you handle them on purpose.

IS NULL, never = NULL

manager_id = NULL is never true, not even when manager_id is NULL. Use IS NULL and IS NOT NULL.

SELECT name FROM employees WHERE manager_id IS NULL;

COALESCE for a fallback

COALESCE(a, b, c) returns the first argument that isn't NULL. All arguments must be the same type, so cast numbers to text if the fallback is a word.

SELECT name, COALESCE(manager_id::text, 'no manager') AS manager
FROM employees;

How aggregates treat NULL

COUNT(*) counts rows; COUNT(column) skips NULLs. SUM and AVG ignore NULLs too, which is usually right but changes an average if you meant missing to be zero.

Try it

This runs real PostgreSQL in your browser on the practice tables. Change it and run it again.

try it

Starting Postgres in your browser…

Real Postgres in your browser. Edit the query and run it, or open it in the full playground.

Common mistakes

  • Writing = NULL or != NULL.
  • NOT IN with a subquery that can return NULL: it returns no rows at all. Use NOT EXISTS.
  • COALESCE(number_column, 'text'), which fails on mismatched types.

Practice drills

Reading it isn't enough. Solve these and it'll still be there in an interview.