How does SQL handle NULL values? | Interview Question

Q: How does SQL handle NULL values?

**Target Audience:** Beginners who know SELECT and WHERE, and anyone who has been surprised by a query that "lost" rows. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16. MySQL, SQL Server and Oracle differences are cited where they matter. > **How to read this doc:** Each idea is explained in plain language first, then tagged with `📌 Interview term:` so you learn the words an interviewer expects. Every result shown was produced by actually running the query; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First Imagine a paper form with a box labelled "Bonus". For Asha someone wrote 5000. For Dara someone wrote 0. For Ben the box is blank. Is Ben's bonus bigger than 1500? You cannot say. It is not "no": the honest answer is "I don't know". Is Ben's bonus the same as Eli's (also blank)? Again, you don't know: two blank boxes are not proof of two e

The problem

Question presented to candidate: "Some of our employees have no bonus recorded and some have no manager. Explain what NULL actually means in SQL, why WHERE bonus = NULL returns nothing, and which other places in a query NULL quietly changes the result."

What a strong answer should cover:

NULL means unknown or missing, not zero and not an empty string; it is a marker, not a value
Three-valued logic: comparisons with NULL give UNKNOWN, and WHERE, ON and HAVING keep only rows that are TRUE, so = NULL matches nothing; use IS NULL / IS NOT NULL or the null-safe IS [NOT] DISTINCT FROM
NULL propagates through arithmetic and || concatenation; NOT (UNKNOWN) is still UNKNOWN, so a filter and its negation can both miss a row
Where NULLs group together instead: GROUP BY, DISTINCT and set operations put all NULLs in one bucket; UNIQUE allows many NULLs (PostgreSQL 15 added NULLS NOT DISTINCT)
Engine details: NULL sorts first in SQLite and last in PostgreSQL ascending (NULLS FIRST/LAST), Oracle treats '' as NULL, aggregates skip NULLs, and NOT IN with a NULL in the list returns no rows

Code / implementation expected: Optional — show = NULL vs IS NULL and one case where a filter and its negation together lose rows.

Schema used by the examples:

null

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who know SELECT and WHERE, and anyone who has been surprised by a query that "lost" rows. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16. MySQL, SQL Server and Oracle differences are cited where they matter.

How to read this doc: Each idea is explained in plain language first, then tagged with 📌 Interview term: so you learn the words an interviewer expects. Every result shown was produced by actually running the query; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

Imagine a paper form with a box labelled "Bonus". For Asha someone wrote 5000. For Dara someone wrote 0. For Ben the box is blank.

Is Ben's bonus bigger than 1500? You cannot say. It is not "no": the honest answer is "I don't know". Is Ben's

The solution is hidden so you can try first

14 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQL= NULL matches nothing; IS NULL and IS NOT NULL do the job

SQLA filter and its NOT together still miss the NULL rows

SQLNULL spreads through expressions, groups together, and sorts first in SQLite

PostgreSQL: the three-valued truth table with real booleans

SELECT v.a, v.b,
       v.a AND v.b AS a_and_b,
       v.a OR v.b AS a_or_b,
       NOT v.a AS not_a,
       (v.a = v.b) IS NULL AS eq_is_unknown,
       v.a IS NOT DISTINCT FROM v.b AS null_safe_eq
FROM (VALUES (true, NULL::boolean),
             (false, NULL::boolean),
             (NULL::boolean, NULL::boolean)) AS v(a, b);

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.