SQL Interview Prep · Lesson

IS NULL, IS NOT NULL and NULL-Safe Equality

Correctly testing for NULL and the NULL-safe operators per dialect.

IS NULL, IS NOT NULL and NULL-Safe Equality is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Testing for NULL the Right Way

The previous lesson proved you cannot use = to find NULLs. So how do you actually test for them? With the dedicated predicates IS NULL and IS NOT NULL.

These are the only correct, portable way to check for missing values, and interviewers will reject col = NULL every time they see it.

This lesson covers IS NULL, IS NOT NULL, the IS DISTINCT FROM family, and the dialect-specific NULL-safe equality operators. Knowing the cross-database differences is a strong senior signal.

IS NULL and IS NOT NULL

IS NULL returns TRUE when the value is NULL and FALSE otherwise. Crucially, it never returns UNKNOWN, so it is safe to use directly in WHERE.

IS NOT NULL is its exact complement: TRUE for any actual value, FALSE for NULL.

These predicates are the workhorses of NULL handling. They are standard SQL and behave identically across MySQL, Postgres, SQL Server, Oracle, and SQLite.

-- Find employees with no recorded bonus
SELECT name FROM employees WHERE bonus IS NULL;

-- Find employees that do have a bonus
SELECT name FROM employees WHERE bonus IS NOT NULL;

Why col = NULL Is Always Wrong

A guaranteed interview gotcha: a candidate writes WHERE bonus = NULL expecting it to find missing bonuses. It returns zero rows.

Remember three-valued logic: bonus = NULL is UNKNOWN for every row, including the NULL ones, because nothing equals an unknown. WHERE keeps only TRUE, so nothing matches.

Some databases in non-standard modes will quietly rewrite = NULL to IS NULL, but you must never rely on that. Always write IS NULL explicitly.

-- WRONG: returns zero rows, bonus = NULL is UNKNOWN for all
SELECT name FROM employees WHERE bonus = NULL;

-- RIGHT:
SELECT name FROM employees WHERE bonus IS NULL;

Counting NULLs and Non-NULLs

A common analyst task is data-quality auditing: how complete is a column? Combine IS NULL with COUNT to report missing values.

Note the contrast: COUNT(*) counts every row, while COUNT(bonus) counts only non-NULL bonuses. The difference between them equals the NULL count, a fact we revisit in the aggregates lesson.

SELECT
  COUNT(*)                                AS total_rows,
  COUNT(bonus)                            AS with_bonus,
  COUNT(*) - COUNT(bonus)                 AS missing_bonus,
  SUM(CASE WHEN bonus IS NULL THEN 1 ELSE 0 END) AS missing_check
FROM employees;

The Problem NULL-Safe Equality Solves

Suppose you want to match two columns and treat 'both NULL' as a match. Plain a = b fails: when both are NULL the result is UNKNOWN, so the pair is excluded even though intuitively they are 'the same'.

This shows up when comparing an old row to a new row to detect changes, or when joining on optional columns. You need a comparison where NULL equals NULL is TRUE and NULL vs a value is FALSE. That is what NULL-safe equality provides.

-- Goal: change-detection where two NULLs count as equal
-- Plain equality fails when both sides are NULL:
--   NULL = NULL -> UNKNOWN (treated as not-equal)
SELECT * FROM old_t o JOIN new_t n ON o.id = n.id
WHERE o.note = n.note;  -- misses rows where both notes are NULL

IS DISTINCT FROM (Standard SQL)

The ANSI-standard NULL-safe comparison is IS DISTINCT FROM and its inverse IS NOT DISTINCT FROM. They are supported in Postgres, SQL Server (2022+), and others.

a IS NOT DISTINCT FROM b means 'equal, with NULL = NULL counting as equal'.
a IS DISTINCT FROM b means 'different, treating NULL as a normal value'.

These always return TRUE or FALSE, never UNKNOWN, so they are safe everywhere a predicate is expected.

-- TRUE when notes match, including both NULL
SELECT * FROM old_t o JOIN new_t n ON o.id = n.id
WHERE o.note IS NOT DISTINCT FROM n.note;

-- TRUE when notes differ (NULL vs value counts as different)
SELECT * FROM old_t o JOIN new_t n ON o.id = n.id
WHERE o.note IS DISTINCT FROM n.note;

MySQL's <=> Operator

MySQL ships a compact NULL-safe equality operator written as <=> (the spaceship operator).

a <=> b returns 1 (TRUE) when both sides are equal or both are NULL, and 0 (FALSE) otherwise. It is the MySQL equivalent of IS NOT DISTINCT FROM.

If an interviewer asks for NULL-safe matching in MySQL specifically, this is the idiomatic answer.

-- MySQL: 1 when both equal or both NULL
SELECT (NULL <=> NULL) AS both_null,   -- 1
       (NULL <=> 5)    AS null_vs_val, -- 0
       (5 <=> 5)       AS val_eq;      -- 1

SELECT * FROM old_t o JOIN new_t n ON o.id = n.id
WHERE o.note <=> n.note;

Cross-Dialect Cheat Sheet

Interviewers respect candidates who know portability boundaries. Here is the NULL-safe equality map:

ANSI / Postgres / SQL Server 2022+: IS NOT DISTINCT FROM
MySQL / MariaDB: <=>
SQLite: IS and IS NOT work as NULL-safe equality
Oracle: no native operator; emulate with DECODE(a, b, 1, 0) = 1 or COALESCE tricks

When unsure of the engine, fall back to the portable manual form shown next.

-- SQLite NULL-safe equality
SELECT * FROM t WHERE a IS b;      -- TRUE when both NULL
SELECT * FROM t WHERE a IS NOT b;  -- complement

Portable Manual NULL-Safe Match

When no native operator is available, you can build NULL-safe equality from primitives. The portable pattern combines a normal equality with an explicit both-NULL clause.

Read it as: 'they are equal, OR they are both missing.' This works on every database, which makes it a great answer when the interviewer does not pin down a dialect.

SELECT *
FROM old_t o JOIN new_t n ON o.id = n.id
WHERE (o.note = n.note)
   OR (o.note IS NULL AND n.note IS NULL);

-- Alternative using COALESCE with a sentinel that
-- cannot occur in real data:
-- WHERE COALESCE(o.note, '##NULL##') = COALESCE(n.note, '##NULL##')

Deeper Example: NULL-Safe JOIN Keys

A realistic trap: joining on a nullable key. If region can be NULL on both sides, an ordinary equi-join silently drops those pairs because NULL = NULL is UNKNOWN.

If the business rule is 'rows with no region should still match other no-region rows', you must make the join condition NULL-safe. State the assumption out loud in the interview, then choose the operator that matches the engine.

-- Postgres / ANSI: match including both-NULL regions
SELECT a.id, b.id
FROM table_a a
JOIN table_b b
  ON a.region IS NOT DISTINCT FROM b.region;

-- MySQL equivalent: ON a.region <=> b.region

Interview Talking Points

To handle any NULL-testing question cleanly:

Always use IS NULL / IS NOT NULL; never = NULL.
These predicates return only TRUE or FALSE, so they are safe in WHERE.
For 'NULL equals NULL' matching, use IS NOT DISTINCT FROM (ANSI) or <=> (MySQL).
State which dialect you are targeting; offer the portable OR-clause fallback when unsure.

Naming both the standard and the vendor operator shows breadth that screeners notice.

Quick Check

Pick the correct NULL-safe comparison.

Recap

You can now test for NULL correctly:

IS NULL / IS NOT NULL are the only correct, portable NULL tests; they never return UNKNOWN.
col = NULL always yields zero rows; it is a classic interview trap.
NULL-safe equality treats two NULLs as equal: IS NOT DISTINCT FROM (ANSI/Postgres), <=> (MySQL), IS (SQLite).
When no operator exists, use (a = b) OR (a IS NULL AND b IS NULL).

Next: substituting defaults for NULL with COALESCE, NULLIF, and vendor functions like ISNULL.

Frequently asked questions

Is the “IS NULL, IS NOT NULL and NULL-Safe Equality” lesson free?

Yes — the full text of “IS NULL, IS NOT NULL and NULL-Safe Equality” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “IS NULL, IS NOT NULL and NULL-Safe Equality”?

Correctly testing for NULL and the NULL-safe operators per dialect. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “IS NULL, IS NOT NULL and NULL-Safe Equality” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

Three-Valued Logic and UNKNOWN
IS NULL, IS NOT NULL and NULL-Safe Equality
COALESCE, NULLIF and ISNULL
NULLs in Aggregates, Joins and DISTINCT

← Back to SQL Interview Prep