SQL NULL Semantics & Three-Valued Logic: The Traps That Break Every Report

sql null three valued logic is the primitive every reporting bug you can't reproduce eventually traces back to — the row count that's off by a hundred, the join that silently drops half the fact table, the aggregate that's mysteriously smaller than expected, the CHECK constraint that fires when it shouldn't, the UNIQUE index that lets duplicates in. Every SQL engineer knows "NULL means missing" on day one, but knowing why NULL = NULL returns UNKNOWN instead of TRUE, why COUNT(*) and COUNT(col) disagree, why your LEFT JOIN produces NULLs where you didn't expect them, and how IS DISTINCT FROM fixes the null-safe comparison problem is what separates a senior SQL candidate from everyone else. This guide is the honest tour of three-valued logic and what every engine actually does with NULL under the hood.

The tour walks the three-valued logic (TVL) foundation — TRUE, FALSE, and UNKNOWN and how AND, OR, NOT truth tables produce non-intuitive results when NULLs are involved, why WHERE drops UNKNOWN rows (treating them like FALSE) but CHECK allows them (treating like TRUE) so a "positive quantity" constraint mysteriously accepts NULLs. It walks NULL behavior in aggregates — COUNT(*) counts NULL rows, COUNT(col) skips them, SUM/AVG/MIN/MAX all skip NULLs but AVG divides by the non-NULL count, and GROUP BY treats NULLs as equal (unlike =), so all NULL rows cluster into one group. It walks NULL in ORDER BY — Postgres and Oracle default to NULLS FIRST for ASC; MySQL and SQL Server default to NULLS LAST — and how to override. It walks NULL in joins — equi-joins never match on NULL, LEFT JOINs produce NULLs on the unmatched right side. It walks NULL in UNIQUE indexes — Postgres and Oracle allow multiple NULLs by default; SQL Server allows only one; partial indexes handle the rest. Every section ships a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown.

When you want hands-on reps immediately after reading, drill the SQL practice library → for NULL-handling edge cases, sharpen SQL aggregation drills → for the COUNT / SUM / AVG behavior on NULLs, and layer SQL join drills → for the equi-join / LEFT JOIN / anti-join NULL patterns.

On this page

• Why NULL matters in 2026
• Three-valued logic anatomy
• NULL in aggregates, GROUP BY, ORDER BY
• NULL in joins, indexes, uniqueness
• IS DISTINCT FROM + COALESCE + NULLIF + dialect matrix
• Cheat sheet — NULL semantics recipe list
• Frequently asked questions
• Practice on PipeCode

1. Why NULL matters in 2026

The sql null semantics mental model — why NULL is not a value, and why every "missing == NULL" assumption produces a bug

The one-sentence invariant: NULL in SQL is not a value — it is the ABSENCE of a value; every operation on NULL that would produce a value instead produces NULL (or UNKNOWN in boolean contexts), and this "poison" property means a single unhandled NULL can silently corrupt a report, a join, a constraint, or an index — the discipline is to test for NULL explicitly with IS NULL / IS NOT NULL at every boundary where NULL is possible. Every reporting bug you can't reproduce started with someone assuming NULL = 'X' returns FALSE when it actually returns UNKNOWN.

Where NULL bugs actually show up.

• Reports off by rows. A dashboard shows 8,392 orders; the reconciliation query shows 8,415. The difference is 23 orders with NULL customer_id that a WHERE customer_id != 42 filter dropped without warning.
• Joins silently losing data. orders JOIN customers ON o.customer_id = c.id drops all orders with NULL customer_id. LEFT JOIN keeps them; INNER JOIN doesn't. The choice depends on business intent.
• Aggregates surprisingly smaller. SUM(discount) on 100 rows returns a smaller sum than expected because 30 of those rows had NULL discounts, and NULLs are silently skipped.
• CHECK constraints that don't fire. CHECK (quantity > 0) allows NULL quantity because NULL > 0 is UNKNOWN, and UNKNOWN passes CHECK. Only NOT NULL + CHECK together enforce "positive quantity".
• UNIQUE indexes with duplicates. Postgres UNIQUE (email) allows multiple rows with NULL email. If you meant to enforce uniqueness even on missing email, add NOT NULL or use a partial index.
• Aggregations over grouping sets. GROUPING SETS output has NULLs to indicate "aggregated at this grain" — using = NULL in downstream filters loses those rows.
• PIVOT with sparse data. A pivot rotating 4 columns fills unmatched cells with NULL. Downstream SUM(col) skips them; ignore the NULLs.
• Feature-store row filtering. A "recent event" filter WHERE last_event_at > NOW() - INTERVAL '1 day' drops rows where last_event_at IS NULL.
• CDC feed handling. Debezium encodes deletes with NULL after-values. Downstream MERGE must handle NULLs deliberately.

The four "poisonous" operators.

• Arithmetic. x + NULL = NULL. x * NULL = NULL. x / NULL = NULL. And NULL / 0 = NULL (not error). Every arithmetic operation with NULL propagates NULL.
• String concatenation. 'hello' || NULL = NULL. CONCAT('hello', NULL) in most engines returns NULL. Fix — CONCAT_WS('sep', ..., ...) may skip NULLs; check per engine.
• Boolean comparison. x = NULL = UNKNOWN. x <> NULL = UNKNOWN. NULL = NULL = UNKNOWN. Never returns TRUE or FALSE.
• Boolean operators (AND/OR). TRUE AND NULL = NULL. FALSE AND NULL = FALSE. TRUE OR NULL = TRUE. FALSE OR NULL = NULL. Non-intuitive edge cases.

What senior interviewers actually probe.

• Do you know that NULL = NULL is UNKNOWN, not TRUE? Fundamental. If you get this wrong, the interview is over.
• Do you know IS NULL vs = NULL? IS NULL is the null-test. = NULL returns UNKNOWN. Different keywords.
• Do you know WHERE vs CHECK on NULL? WHERE drops UNKNOWN rows; CHECK allows them. The reason CHECK (x > 0) doesn't prevent NULL x.
• Do you know COUNT(*) vs COUNT(col)? COUNT(*) counts rows including NULLs; COUNT(col) counts non-NULL values.
• Do you know GROUP BY treats NULL as equal? All NULLs cluster into one group, unlike WHERE where = NULL is UNKNOWN.
• Do you know ORDER BY NULL ordering per engine? Postgres/Oracle default NULLS FIRST for ASC; MySQL/SQL Server NULLS LAST.
• Do you know IS DISTINCT FROM? Null-safe inequality. NULL IS DISTINCT FROM NULL is FALSE. Useful for MERGE conditional UPDATE.
• Do you know UNIQUE + NULL per engine? Postgres/Oracle allow many NULLs. SQL Server allows one. Partial indexes rescue.
• Do you know FK + NULL? NULL FK means "no reference." Allowed unless the column is NOT NULL.

The three canonical NULL tests.

• x IS NULL — returns TRUE if x is NULL, else FALSE. Always defined.
• x IS NOT NULL — returns TRUE if x is not NULL, else FALSE.
• x IS DISTINCT FROM y — TRUE if they differ (including one being NULL and the other not). FALSE if same (including both NULL). Null-safe inequality.

Worked example — the NULL customer_id that broke the report

Detailed explanation. A junior data engineer writes a filter WHERE customer_id != 42 expecting to see all orders NOT from customer 42. Instead, orders with NULL customer_id are silently dropped.

Question. Given orders(id, customer_id, total) with some rows having NULL customer_id, why does WHERE customer_id != 42 miss them, and how do you fix?

Input.

Code.

-- Bug: misses row 3
SELECT * FROM orders WHERE customer_id != 42;
-- returns: id=2 (43), id=4 (44)
-- id=3 (NULL) silently dropped

-- Fix 1: explicit IS NULL
SELECT * FROM orders WHERE customer_id != 42 OR customer_id IS NULL;

-- Fix 2: IS DISTINCT FROM (null-safe inequality)
SELECT * FROM orders WHERE customer_id IS DISTINCT FROM 42;

-- Fix 3: COALESCE (if you want to treat NULL as some specific value)
SELECT * FROM orders WHERE COALESCE(customer_id, -1) != 42;

Step-by-step explanation.

1. WHERE customer_id != 42. For row 3, NULL != 42 returns UNKNOWN.
2. WHERE clause keeps only TRUE rows. UNKNOWN is treated like FALSE. Row 3 dropped.
3. Fix 1 — add an explicit OR customer_id IS NULL clause. Ugly but explicit.
4. Fix 2 — IS DISTINCT FROM 42 returns TRUE when customer_id is NULL (they differ), so row 3 kept.
5. Fix 3 — COALESCE(customer_id, -1) treats NULL as -1; -1 != 42 returns TRUE; row 3 kept.

Output.

Rule of thumb. Any != or <> filter on a nullable column has a NULL-drop bug. Prefer IS DISTINCT FROM for null-safe inequality.

Worked example — the CHECK constraint that doesn't enforce positivity

Detailed explanation. CHECK (quantity > 0) allows NULL quantity because NULL > 0 is UNKNOWN, which CHECK treats as TRUE.

Question. Show that CHECK (quantity > 0) alone doesn't prevent NULL quantity; then show the fix.

Code.

-- Doesn't enforce positive quantity because NULL passes
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  quantity INT CHECK (quantity > 0)
);
INSERT INTO orders (quantity) VALUES (5);      -- ok
INSERT INTO orders (quantity) VALUES (-3);     -- CHECK fails
INSERT INTO orders (quantity) VALUES (NULL);   -- ALSO OK (surprise)

-- Fix: NOT NULL constraint
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  quantity INT NOT NULL CHECK (quantity > 0)
);

Step-by-step explanation.

1. CHECK (quantity > 0) — for NULL quantity, evaluates to NULL > 0 = UNKNOWN.
2. CHECK constraints treat UNKNOWN as "not FALSE" — they pass. So NULL rows sneak in.
3. WHERE clauses do the opposite — treat UNKNOWN as "not TRUE" and drop.
4. This asymmetry is the source of many CHECK-doesn't-work bugs.
5. Fix — add NOT NULL explicitly. Or CHECK (quantity IS NOT NULL AND quantity > 0).

Output.

Rule of thumb. Always pair CHECK constraints with NOT NULL if you want to prevent NULL. Or write the CHECK to include an explicit NULL test.

Worked example — the join that dropped 20% of the fact table

Detailed explanation. A common bug — INNER JOIN drops fact rows with NULL join keys.

Question. Given orders with 8% NULL customer_id, why does INNER JOIN customers drop them?

Input.

orders (10,000 rows, 800 with NULL customer_id)
customers (500 rows, all with non-NULL id)

Code.

-- Bug: INNER JOIN silently drops NULL-customer_id rows
SELECT o.*, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;
-- returns 9,200 rows (missed 800)

-- Fix: LEFT JOIN keeps NULL-customer_id rows with NULL name
SELECT o.*, c.name
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id;
-- returns 10,000 rows

Step-by-step explanation.

1. INNER JOIN requires c.id = o.customer_id to be TRUE.
2. For NULL customer_id, NULL = c.id is UNKNOWN (never TRUE).
3. INNER JOIN drops those rows.
4. LEFT JOIN keeps all left-side rows; unmatched right side is NULL.
5. Choose based on business intent — "orders that have a customer" (INNER) vs "all orders" (LEFT).

Output.

Rule of thumb. Any JOIN on a nullable column silently drops NULL rows under INNER. Choose LEFT if you need to keep them.

Common beginner mistakes

• Using = NULL instead of IS NULL.
• Assuming CHECK (col > 0) prevents NULL.
• Assuming COUNT(*) and COUNT(col) are the same.
• Using INNER JOIN when LEFT JOIN was needed for NULL preservation.
• Ignoring NULL in aggregates and getting surprising totals.
• Using != filters without accounting for NULL rows.

sql null semantics interview question on NULL-safe comparison

A senior interviewer asks: "Write a query that returns customers whose preferred_lang field differs between the current row and the previous month's snapshot, handling NULL correctly."

Solution Using IS DISTINCT FROM for null-safe compare

WITH pairs AS (
  SELECT
    c.id,
    c.preferred_lang AS current_lang,
    prev.preferred_lang AS prev_lang
  FROM customers c
  LEFT JOIN customers_snapshot_last_month prev
    ON prev.id = c.id
)
SELECT id, current_lang, prev_lang
FROM pairs
WHERE current_lang IS DISTINCT FROM prev_lang;

Step-by-step trace.

Output: Only rows where the value actually changed, including NULL transitions.

Why this works — concept by concept:

• IS DISTINCT FROM is null-safe inequality — returns TRUE when values differ (including NULL vs non-NULL) and FALSE when same (including NULL vs NULL). Correct for change-detection.
• != alone drops NULL rows — returns UNKNOWN when either side is NULL. WHERE clause drops UNKNOWN.
• LEFT JOIN preserves current customers even without snapshot — a new customer's prev_lang is NULL; IS DISTINCT FROM correctly detects change from NULL → some value.
• Consistent across engines — IS DISTINCT FROM is ANSI SQL. Postgres, Oracle native; MySQL 8.0.16+; SQL Server requires CASE workaround.
• Cost — O(N) scan; no NULL-handling code in application.

SQL
Topic — SQL
SQL practice library

Practice →

Practice →

2. Three-valued logic anatomy

The sql null three valued logic foundation — TRUE, FALSE, and UNKNOWN in every boolean context

The mental model in one line: SQL boolean expressions can evaluate to TRUE, FALSE, or UNKNOWN; WHERE clauses keep only TRUE rows (treating UNKNOWN as "not TRUE"); CHECK constraints reject only FALSE rows (treating UNKNOWN as "not FALSE"); and every operator involving NULL propagates UNKNOWN unless the specific operator handles NULL specially (IS NULL, IS DISTINCT FROM).

Slot 1 — the three values.

• TRUE. Standard true.
• FALSE. Standard false.
• UNKNOWN. SQL's third value, represented at the syntax level by NULL in boolean context.
• Every boolean context. WHERE, HAVING, CASE WHEN, CHECK, ON, JOIN, and inside AND / OR / NOT.

Slot 2 — AND truth table.

Key insight: FALSE dominates in AND (if either operand is FALSE, result is FALSE, even if the other is UNKNOWN).

Slot 3 — OR truth table.

Key insight: TRUE dominates in OR (if either operand is TRUE, result is TRUE, even if the other is UNKNOWN).

Slot 4 — NOT truth table.

Key insight: NOT preserves UNKNOWN.

Slot 5 — WHERE vs CHECK.

• WHERE cond — keeps rows where cond is TRUE. UNKNOWN dropped (treated as not-TRUE).
• CHECK cond — rejects rows where cond is FALSE. UNKNOWN accepted (treated as not-FALSE).
• Consequence. WHERE x > 0 drops NULL x. CHECK (x > 0) accepts NULL x.
• Symmetric on HAVING. Same rules as WHERE.
• ON clause of JOIN. Same as WHERE for the join predicate — UNKNOWN rows don't match.

Slot 6 — the IS NULL / IS NOT NULL operators.

• x IS NULL — returns TRUE if x is NULL, else FALSE. Never UNKNOWN.
• x IS NOT NULL — returns TRUE if x is non-NULL, else FALSE.
• Use. Filter for NULLs. Combine with other conditions.
• Common pattern. WHERE x IS NOT NULL AND x > 0 — filter out NULLs explicitly before applying >.

Slot 7 — the IS TRUE / IS FALSE / IS UNKNOWN operators.

• x IS TRUE — TRUE if x is TRUE, else FALSE. Never UNKNOWN.
• x IS NOT TRUE — TRUE if x is FALSE or UNKNOWN.
• x IS FALSE — TRUE if x is FALSE.
• x IS UNKNOWN — TRUE if x is NULL (in boolean context).
• Rarely needed but useful when you specifically want to distinguish UNKNOWN from FALSE.

Slot 8 — the IN operator with NULL.

• x IN (1, 2, NULL) — TRUE if x is 1 or 2; UNKNOWN if x doesn't match and NULL is in the list.
• x NOT IN (1, 2, NULL) — always UNKNOWN because x != NULL is UNKNOWN.
• Consequence. WHERE x NOT IN (SELECT id FROM subquery) where subquery contains NULL — returns zero rows.
• Fix. Add WHERE id IS NOT NULL to the subquery, or use NOT EXISTS (which handles NULL correctly).

Slot 9 — CASE with NULL.

• CASE WHEN x = NULL THEN 1 ELSE 0 END — always returns 0 because x = NULL is UNKNOWN, never TRUE.
• CASE WHEN x IS NULL THEN 1 ELSE 0 END — returns 1 for NULL, 0 for non-NULL.
• CASE WHEN x THEN 1 ELSE 0 END — where x is boolean. UNKNOWN treats as not-TRUE, so returns 0.
• CASE x WHEN NULL THEN 1 END — never matches; the simple CASE form uses = semantics.

Slot 10 — NULL propagation in arithmetic.

• x + NULL = NULL.
• x * NULL = NULL (even if x = 0, since 0 * anything logic doesn't apply).
• NULL / 0 = NULL (not division-by-zero error).
• NULL || 'x' = NULL for string concatenation.
• Fix — COALESCE to handle NULL: x + COALESCE(y, 0).

Common beginner mistakes

• Writing x = NULL expecting TRUE.
• Assuming WHERE NOT (x = 5) returns rows where x != 5 — misses NULL x.
• Forgetting NULL propagation in arithmetic.
• Assuming CHECK rejects NULL.
• Writing x NOT IN (NULL, ...) expecting normal behavior.

Worked example — the NOT IN trap with NULL in subquery

Detailed explanation. x NOT IN (subquery) with NULL in the subquery result returns zero rows because x != NULL is UNKNOWN.

Question. Show the bug and fix.

Input.

orders (100 rows)
suspended_customers (5 rows, ONE OF THEM has NULL customer_id)

Code.

-- Bug: NULL in subquery makes NOT IN return zero rows
SELECT * FROM orders
WHERE customer_id NOT IN (SELECT customer_id FROM suspended_customers);
-- returns 0 rows (surprise)

-- Fix 1: filter NULL from subquery
SELECT * FROM orders
WHERE customer_id NOT IN (
  SELECT customer_id FROM suspended_customers WHERE customer_id IS NOT NULL
);

-- Fix 2: NOT EXISTS (handles NULL correctly)
SELECT * FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM suspended_customers s WHERE s.customer_id = o.customer_id
);

Step-by-step explanation.

1. NOT IN (subquery). For each orders.customer_id, check if it's in the subquery result.
2. Subquery result includes NULL.
3. x NOT IN (1, 2, NULL) is equivalent to x != 1 AND x != 2 AND x != NULL.
4. x != NULL = UNKNOWN. AND with UNKNOWN can never be TRUE (short-circuits to UNKNOWN or FALSE).
5. Result — no row passes; zero rows returned.
6. Fix 1 — remove NULL from the subquery.
7. Fix 2 — NOT EXISTS uses correlated logic; doesn't have the NULL propagation issue.

Output.

Rule of thumb. Never use NOT IN (subquery) unless the subquery is guaranteed NULL-free. Use NOT EXISTS instead.

Worked example — WHERE vs CHECK on NULL

Detailed explanation. Same predicate, opposite treatment of NULL rows.

Question. Show the same predicate quantity > 0 behaves differently in WHERE vs CHECK.

Code.

CREATE TABLE items (id INT, quantity INT CHECK (quantity > 0));
INSERT INTO items VALUES (1, 5);       -- ok
INSERT INTO items VALUES (2, NULL);    -- ok! CHECK allows NULL
INSERT INTO items VALUES (3, -1);      -- REJECTED (quantity > 0 is FALSE)

-- Verify
SELECT * FROM items WHERE quantity > 0;
-- returns only id=1 (id=2 dropped by WHERE)

SELECT * FROM items;
-- returns id=1 (5) and id=2 (NULL)

Step-by-step explanation.

1. Row (2, NULL) — CHECK evaluates NULL > 0 = UNKNOWN. CHECK passes UNKNOWN.
2. Row (3, -1) — CHECK evaluates -1 > 0 = FALSE. CHECK rejects.
3. SELECT with WHERE quantity > 0 — evaluates NULL > 0 = UNKNOWN. WHERE drops.
4. Same predicate, opposite behavior. This is the WHERE/CHECK asymmetry.
5. Fix CHECK to reject NULL — add NOT NULL or write CHECK (quantity IS NOT NULL AND quantity > 0).

Output.

Rule of thumb. WHERE drops UNKNOWN; CHECK allows UNKNOWN. Add NOT NULL when you want CHECK to enforce.

Worked example — AND / OR with NULL

Detailed explanation. The truth tables at work in real queries.

Question. Show that NOT (x = 5) is not the same as x != 5 when NULL is possible.

Input.

data:
  id | val
  1  | 5
  2  | 6
  3  | NULL

Code.

SELECT * FROM data WHERE x != 5;
-- returns id=2 (6). Misses id=3 (NULL) because NULL != 5 = UNKNOWN.

SELECT * FROM data WHERE NOT (x = 5);
-- returns id=2 (6). Also misses id=3 because NOT (NULL = 5) = NOT (UNKNOWN) = UNKNOWN.

SELECT * FROM data WHERE x != 5 OR x IS NULL;
-- returns id=2, id=3. Explicit NULL handling.

SELECT * FROM data WHERE x IS DISTINCT FROM 5;
-- returns id=2, id=3. Null-safe.

Step-by-step explanation.

1. Both x != 5 and NOT (x = 5) produce UNKNOWN for NULL x. WHERE drops UNKNOWN.
2. Explicit OR x IS NULL handles NULL.
3. IS DISTINCT FROM is the null-safe primitive.
4. Use IS DISTINCT FROM when you want NULL to count as "different from any value."

Output.

Rule of thumb. For "different from" logic on nullable columns, prefer IS DISTINCT FROM.

sql null three valued logic interview question on TVL correctness

A senior interviewer asks: "You're refactoring a query to eliminate the NULL-drop bug. Show the pattern and explain each choice."

Solution Using systematic NULL handling per column

-- Original (buggy):
SELECT o.id, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country != 'US'
  AND o.status = 'shipped'
  AND o.discount_pct != 0;

-- Refactored (NULL-safe):
SELECT o.id, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id       -- INNER JOIN intentional (require customer)
WHERE c.country IS DISTINCT FROM 'US'          -- Keep NULL-country customers
  AND o.status IS NOT DISTINCT FROM 'shipped'  -- Match exact 'shipped'
  AND COALESCE(o.discount_pct, 0) != 0;        -- Treat NULL discount as 0

Step-by-step trace.

Output: Correct rows including intentionally-preserved NULL cases.

Why this works — concept by concept:

• IS DISTINCT FROM for null-safe inequality — preserves NULL rows when the business intent is "not US or unknown country".
• COALESCE(col, default) — treats NULL as a specific value. Makes NULL-vs-0 arithmetic explicit.
• Explicit choice per column — different columns may want different NULL semantics.
• Documentation via structure — the refactored SQL reads like a business spec.
• Cost — negligible; predicates evaluate the same.

SQL
Topic — SQL
SQL practice library

Practice →

Practice →

3. NULL in aggregates, GROUP BY, ORDER BY

null in aggregates and null in group by — how COUNT, SUM, AVG treat NULL, and why GROUP BY treats NULLs as equal

The mental model in one line: aggregates (SUM, AVG, MIN, MAX, COUNT(col)) silently skip NULL values, COUNT(*) counts every row including NULLs, GROUP BY treats NULLs as equal (so all NULL rows form one group — unlike = which returns UNKNOWN), and ORDER BY sorts NULLs to one end whose default depends on the engine (Postgres/Oracle default NULLS FIRST for ASC; MySQL/SQL Server default NULLS LAST).

Slot 1 — COUNT(*) vs COUNT(col).

• COUNT(*) — counts every row in the group, including rows with NULL in every column.
• COUNT(col) — counts rows where col is not NULL.
• COUNT(DISTINCT col) — counts distinct non-NULL values.
• COUNT(1) vs COUNT(*) — identical on all engines. Historical performance concern is myth.
• Common bug — using COUNT(col) when you mean row count. If col has NULLs, you undercount.

Slot 2 — SUM, AVG, MIN, MAX with NULL.

• SUM(col) — sum of non-NULL values. Returns NULL if all values are NULL.
• AVG(col) — sum of non-NULL / count of non-NULL. Skips NULL from both numerator and denominator.
• MIN(col) / MAX(col) — of non-NULL values. Returns NULL if all NULL.
• SUM(...) OVER (...) windows — same NULL skip behavior.
• Common bug — assuming AVG divides by row count. It divides by non-NULL count.

Slot 3 — the AVG gotcha.

• Setup. table t(x) with rows: 10, 20, NULL, NULL.
• AVG(x) — (10 + 20) / 2 = 15.
• SUM(x) / COUNT(*) — 30 / 4 = 7.5 (different!).
• SUM(x) / COUNT(x) — 30 / 2 = 15.
• AVG(COALESCE(x, 0)) — (10 + 20 + 0 + 0) / 4 = 7.5 (treats NULL as 0).
• Choose based on intent. If NULL means "unknown value, don't count," AVG is correct. If NULL means "zero," COALESCE first.

Slot 4 — GROUP BY with NULL.

• All NULLs cluster into one group. GROUP BY uses IS NOT DISTINCT FROM semantics — NULLs compare as equal for grouping.
• This is different from =. WHERE col = 'X' doesn't match NULL; GROUP BY on col groups NULL rows together.
• Result — the NULL group appears once in output with NULL as its key.
• Downstream filter — WHERE key = NULL doesn't match the NULL group; use WHERE key IS NULL.
• DISTINCT and GROUP BY behave the same. SELECT DISTINCT col includes NULL exactly once.

Slot 5 — HAVING with NULL.

• HAVING follows WHERE semantics. UNKNOWN drops the group.
• HAVING SUM(x) > 100 — for a group with all NULL x, SUM(x) is NULL. NULL > 100 is UNKNOWN. Group dropped.
• HAVING SUM(x) > 100 OR SUM(x) IS NULL — keeps the all-NULL group.

Slot 6 — ORDER BY with NULL.

• Postgres default. ASC → NULLS FIRST. DESC → NULLS LAST.
• Oracle default. Same as Postgres.
• MySQL default. ASC → NULLS FIRST. DESC → NULLS LAST. (Same as Postgres, contrary to some references.)
• SQL Server default. ASC → NULLS FIRST. DESC → NULLS LAST.

Wait — let me get this right. Actually:

• Postgres. ASC → NULLS LAST; DESC → NULLS FIRST. (Reverse of ANSI default.)

Actually engines vary. Let me lay it out properly:

• Postgres — ASC → NULLS LAST; DESC → NULLS FIRST. Override via ORDER BY col ASC NULLS FIRST.
• Oracle — ASC → NULLS LAST; DESC → NULLS FIRST. Same as Postgres.
• MySQL — ASC → NULLS FIRST; DESC → NULLS LAST. Opposite of Postgres.
• SQL Server — ASC → NULLS FIRST; DESC → NULLS LAST. Same as MySQL.
• BigQuery — ASC → NULLS FIRST; DESC → NULLS LAST.
• Snowflake — ASC → NULLS LAST; DESC → NULLS FIRST. Same as Postgres.
• Never rely on defaults. Always spell out NULLS FIRST / NULLS LAST.

Slot 7 — the safest ORDER BY pattern.

• Always spell it out. ORDER BY col ASC NULLS LAST — portable, explicit.
• NULLS FIRST / NULLS LAST supported on. Postgres, Oracle, MySQL 8+, BigQuery, Snowflake, Databricks, DuckDB. Not SQL Server (needs CASE workaround).
• SQL Server workaround. ORDER BY (CASE WHEN col IS NULL THEN 1 ELSE 0 END), col.
• Combined with DESC. ORDER BY col DESC NULLS LAST puts non-NULL descending, NULLs at end.

Slot 8 — window function NULL propagation.

• LAG(col) on first row — returns NULL (no previous row).
• LEAD(col) on last row — returns NULL.
• SUM() OVER (ORDER BY t) with NULL data — NULL values skipped as in aggregates.
• FIRST_VALUE(col) OVER (... IGNORE NULLS) — skips NULLs when picking first non-NULL. Note the IGNORE NULLS syntax not universal.

Slot 9 — grouping sets and NULL semantics.

• From Blog262. GROUPING SETS output has NULL to indicate "aggregated at this grain."
• GROUPING(col) — distinguishes subtotal-NULL from data-NULL.
• Formatting. CASE WHEN GROUPING(col) = 1 THEN 'All' ELSE col END.

Slot 10 — common aggregation bugs.

• Using COUNT(col) when you mean COUNT(*).
• Divide-by-zero via SUM(x) / COUNT(x) when COUNT(x) is 0 (all NULL).
• Assuming SUM returns 0 for all-NULL — it returns NULL. Use COALESCE(SUM(x), 0).
• ORDER BY without NULLS FIRST/LAST when NULL ordering matters.
• HAVING that drops all-NULL groups.

Common beginner mistakes

• Assuming COUNT(*) and COUNT(col) are the same.
• Assuming AVG divides by row count.
• Assuming SUM of all-NULL returns 0.
• Not spelling out NULLS FIRST / LAST in ORDER BY.
• Using WHERE key = NULL after GROUP BY to filter the NULL group.

Worked example — the COUNT(*) vs COUNT(col) surprise

Detailed explanation. Different aggregates give different answers on the same data.

Question. Given a orders table with 100 rows, 15 having NULL customer_id, show COUNT(*) vs COUNT(customer_id).

Code.

SELECT
  COUNT(*) AS total_rows,               -- 100
  COUNT(customer_id) AS orders_with_customer,   -- 85
  COUNT(DISTINCT customer_id) AS unique_customers,  -- ~50 non-NULL
  100 - COUNT(customer_id) AS null_customer_rows  -- 15
FROM orders;

Step-by-step explanation.

1. COUNT(*) — all rows regardless of NULL.
2. COUNT(customer_id) — only rows where customer_id is NOT NULL.
3. COUNT(DISTINCT customer_id) — distinct non-NULL customer_ids.
4. Difference COUNT(*) - COUNT(col) gives NULL row count.

Output.

Rule of thumb. Prefer COUNT(*) for row count. Use COUNT(col) only when you specifically want non-NULL count.

Worked example — GROUP BY clusters NULLs

Detailed explanation. GROUP BY treats NULLs as equal — all NULL rows form one group.

Question. Show that GROUP BY groups NULLs into one bucket.

Code.

data:
  id | region
  1  | US
  2  | US
  3  | NULL
  4  | EU
  5  | NULL

SELECT region, COUNT(*) FROM data GROUP BY region;
-- region | count
-- US     | 2
-- EU     | 1
-- NULL   | 2   (all NULLs in one group)

Step-by-step explanation.

1. GROUP BY region uses IS NOT DISTINCT FROM semantics — NULLs compare as equal.
2. Rows 3 and 5 (both NULL) group together.
3. Output has NULL as a group key.
4. Downstream filter WHERE region = 'NULL' (string literal) or WHERE region IS NULL gets the group.

Output.

Rule of thumb. GROUP BY treats NULLs as equal for grouping. Use IS NULL in HAVING to filter the NULL group.

Worked example — SUM returns NULL for all-NULL groups

Detailed explanation. SUM of all-NULL values is NULL, not 0. Use COALESCE to fix.

Code.

data:
  id | discount
  1  | NULL
  2  | NULL
  3  | 10

SELECT SUM(discount) FROM data;
-- Returns: 10 (rows with NULL skipped)

SELECT SUM(discount) FROM data WHERE id IN (1, 2);
-- Returns: NULL (all NULL)

SELECT COALESCE(SUM(discount), 0) FROM data WHERE id IN (1, 2);
-- Returns: 0 (COALESCE handles all-NULL)

Step-by-step explanation.

1. When all input rows are NULL, SUM returns NULL, not 0.
2. This trips up downstream queries that assume 0 for empty aggregates.
3. Fix — wrap in COALESCE(SUM(x), 0).

Output. Above.

Rule of thumb. For "always return a number" semantics, use COALESCE(SUM(x), 0).

null in aggregates interview question on NULL-safe aggregation

A senior interviewer asks: "Compute per-customer average order value, handling customers with no orders. Report the correct AVG even for zero-order customers."

Solution Using LEFT JOIN + COALESCE for correct zero-order handling

SELECT
  c.id,
  c.name,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS total_revenue,
  COALESCE(AVG(o.total), 0) AS avg_order_value,
  -- Note: AVG gives NULL if no orders; COALESCE handles.
  CASE
    WHEN COUNT(o.id) = 0 THEN 0
    ELSE SUM(o.total) / COUNT(o.id)
  END AS avg_via_ratio
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Step-by-step trace.

Output: Each customer with correct AVG, no NULLs, no divide-by-zero.

Why this works — concept by concept:

• LEFT JOIN keeps zero-order customers — INNER JOIN would drop them.
• COALESCE(SUM, 0) — handles the "all NULL group" case where SUM returns NULL.
• CASE WHEN COUNT = 0 — explicit divide-by-zero guard.
• AVG naturally skips NULLs — no explicit COALESCE needed inside AVG.

SQL
Topic — aggregation
SQL aggregation drills

Practice →

Practice →

4. NULL in joins, indexes, uniqueness

null in joins — how equi-joins never match NULL, and how UNIQUE indexes handle NULL differently per engine

The mental model in one line: equi-joins on NULL columns never produce a match because NULL = NULL is UNKNOWN; LEFT JOIN keeps the left side and NULLs the right when no match exists; UNIQUE indexes on nullable columns behave differently across engines — Postgres/Oracle allow multiple NULLs (treating them as "distinct"), SQL Server allows only one NULL (treating them as "equal" for the constraint); PRIMARY KEY always disallows NULL.

Slot 1 — equi-joins and NULL.

• t1 JOIN t2 ON t1.x = t2.x — for rows where t1.x is NULL, NULL = t2.x is UNKNOWN, no match.
• Same for t2.x NULL.
• Consequence. INNER JOIN drops rows on either side where the join column is NULL.
• t1 LEFT JOIN t2 ON t1.x = t2.x — keeps all t1 rows; when no match, t2 columns are NULL.
• t1 RIGHT JOIN t2 ... — symmetric.
• t1 FULL OUTER JOIN t2 ... — keeps unmatched rows on both sides.

Slot 2 — LEFT JOIN semantics with NULL.

• Case A — t1.x is NULL. No match on right; t2 columns NULL.
• Case B — t2.x is NULL. From t1's side, the join predicate t1.x = NULL is UNKNOWN; no match; t2 NULL columns.
• Case C — both are NULL. Same as A/B; no match.
• Anti-join pattern. LEFT JOIN ... WHERE t2.pk IS NULL — keeps rows with no match. But watch — this also drops rows where t2 columns are naturally NULL.

Slot 3 — non-equi joins with NULL.

• t1 JOIN t2 ON t1.x < t2.x — for NULL, NULL < t2.x is UNKNOWN, no match.
• Range join — same semantics.
• t1 JOIN t2 ON t1.x = t2.x OR (t1.x IS NULL AND t2.x IS NULL) — explicit null-safe join. Verbose but sometimes needed.
• Better — t1 JOIN t2 ON t1.x IS NOT DISTINCT FROM t2.x (Postgres, MySQL 8, SQL Server).

Slot 4 — the anti-join pattern for "not in."

• LEFT JOIN ... WHERE right.pk IS NULL. Efficient anti-join. Right's PK is guaranteed non-NULL when the join matched.
• Careful. Choose a truly non-nullable column for the IS NULL test.
• Alternative. NOT EXISTS (SELECT 1 FROM right WHERE ...). Same result; often clearer.

Slot 5 — UNIQUE index and NULL per engine.

• Partial unique index (Postgres). CREATE UNIQUE INDEX ON t (col) WHERE col IS NOT NULL — allows multiple NULLs anywhere; enforces uniqueness on non-NULL only. Rare intent.
• Enforcing uniqueness even for NULL. Add NOT NULL to the column.
• SQL Server filtered index. CREATE UNIQUE INDEX ON t (col) WHERE col IS NOT NULL — same as Postgres partial.

Slot 6 — PRIMARY KEY and NULL.

• PK is NOT NULL UNIQUE. Every engine disallows NULL in PK.
• Composite PK. All columns are NOT NULL. Missing any one is an error.
• Auto-increment / SERIAL. Sets NOT NULL and generates non-NULL values.

Slot 7 — Foreign key and NULL.

• NULL FK is allowed unless column is NOT NULL. NULL means "no reference."
• FK check happens only for non-NULL values. NULL bypasses the FK constraint.
• On DELETE. If the FK column is NOT NULL, ON DELETE CASCADE / RESTRICT applies. If nullable, ON DELETE SET NULL is common.
• Common pattern. customer_id INT REFERENCES customers(id) — nullable; NULL means unauthenticated user.

Slot 8 — index scans on nullable columns.

• B-tree indexes include NULLs by default on Postgres, Oracle, InnoDB. Query WHERE col IS NULL uses the index.
• Oracle historically excluded NULL from indexes. Function-based NVL(col, ...) index for NULL queries.
• SQL Server — includes NULL in indexes.
• WHERE col IS NULL on indexed column. Usually fast — index seek.
• WHERE col = X — planner uses index for equality; skips NULL naturally.

Slot 9 — grouping and NULL in composite keys.

• GROUP BY (a, b) with NULL in b. NULL groups together as before.
• DISTINCT on composite — same.
• HAVING on aggregate NULL — same as WHERE semantics.

Slot 10 — NULL and OUTER APPLY / LATERAL JOIN.

• LEFT LATERAL / OUTER APPLY — for each left row, run right query. If right returns no rows, columns are NULL.
• Similar to LEFT JOIN. Applies for correlated subqueries.

Common beginner mistakes

• Assuming INNER JOIN keeps NULL-key rows.
• Using WHERE = NULL after LEFT JOIN to find unmatched rows.
• Not knowing SQL Server allows only one NULL in UNIQUE.
• Setting NOT NULL default on FK columns when NULL semantics were intended.
• Ignoring NULL in composite unique constraints.

Worked example — the LEFT JOIN anti-pattern for "orders without customer"

Detailed explanation. Find orders whose customer doesn't exist (data quality check).

Question. Given orders with FK to customers, find orphaned orders (customer_id references nonexistent customer).

Code.

-- Anti-join via LEFT JOIN + IS NULL
SELECT o.*
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL         -- unmatched rows
  AND o.customer_id IS NOT NULL;  -- exclude legitimate NULL customer_id

-- Equivalent NOT EXISTS
SELECT o.*
FROM orders o
WHERE o.customer_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id);

Step-by-step explanation.

1. LEFT JOIN keeps all orders. For unmatched right side, c.id is NULL.
2. WHERE c.id IS NULL — keeps only unmatched rows.
3. AND o.customer_id IS NOT NULL — exclude orders where customer_id was legitimately NULL (unauthenticated user).
4. Alternative — NOT EXISTS. Same result, sometimes clearer.
5. Result — orphaned orders (referring to nonexistent customer).

Output. Orphaned orders only.

Rule of thumb. LEFT JOIN + IS NULL is the classic anti-join. Watch for the NULL customer_id side condition.

Worked example — Postgres UNIQUE allowing multiple NULLs

Detailed explanation. Postgres UNIQUE constraint allows multiple NULLs. If you need "at most one NULL", use a partial unique index or NOT NULL.

Code.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE          -- allows multiple NULL emails
);

INSERT INTO users (email) VALUES ('a@x.com');    -- ok
INSERT INTO users (email) VALUES ('b@x.com');    -- ok
INSERT INTO users (email) VALUES (NULL);         -- ok
INSERT INTO users (email) VALUES (NULL);         -- ALSO OK (multiple NULLs)
INSERT INTO users (email) VALUES ('a@x.com');    -- FAILS (duplicate)

-- Enforce "at most one NULL"
CREATE UNIQUE INDEX users_email_null_unique
  ON users ((TRUE)) WHERE email IS NULL;
-- Now only one NULL row allowed.

Step-by-step explanation.

1. Postgres UNIQUE treats NULLs as "distinct" — multiple NULLs allowed.
2. Non-NULL uniqueness enforced.
3. To enforce "at most one NULL", partial unique index on a constant expression TRUE scoped to NULL rows.
4. SQL Server needs a filtered index for the same behavior.
5. MySQL requires an application-level check.

Output. Multiple NULLs allowed by default. Partial index enforces single NULL.

Rule of thumb. Know your engine's UNIQUE + NULL behavior. Partial index is the escape hatch.

Worked example — IS NOT DISTINCT FROM for null-safe join

Detailed explanation. When you want to join on columns where both being NULL should match.

Code.

-- Standard equi-join (drops NULL rows)
SELECT * FROM t1 JOIN t2 ON t1.x = t2.x;

-- Null-safe join
SELECT * FROM t1 JOIN t2 ON t1.x IS NOT DISTINCT FROM t2.x;

-- Or explicit
SELECT * FROM t1 JOIN t2 ON t1.x = t2.x OR (t1.x IS NULL AND t2.x IS NULL);

Step-by-step explanation.

1. IS NOT DISTINCT FROM — true if both same (including both NULL).
2. Preserves rows with both sides NULL.
3. Rarely wanted for typical joins; useful for change-detection or diff queries.
4. MySQL alternative — <=> operator. t1.x <=> t2.x = same semantics.

Output. Matched rows including NULL-NULL matches.

Rule of thumb. IS NOT DISTINCT FROM is null-safe equality. Use for change-detection joins.

null in indexes interview question on UNIQUE index design

A senior interviewer asks: "Design a users table where email is optional but if provided, must be unique. Then enforce it on Postgres and SQL Server."

Solution Using partial unique index on both engines

-- Postgres
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT
);
CREATE UNIQUE INDEX users_email_unique_partial
  ON users (email) WHERE email IS NOT NULL;

-- SQL Server
CREATE TABLE users (
  id INT IDENTITY PRIMARY KEY,
  email NVARCHAR(255)
);
CREATE UNIQUE INDEX users_email_unique_filtered
  ON users (email) WHERE email IS NOT NULL;

Step-by-step trace.

Output: Email uniqueness enforced for non-NULL; multiple NULLs allowed.

Why this works — concept by concept:

• Partial / filtered unique index — indexes only rows meeting the WHERE condition. NULL rows not indexed.
• UNIQUE constraint applied within the index scope — only non-NULL emails must be unique.
• NULL rows bypass the index — multiple NULLs allowed without constraint conflict.
• Portable pattern — Postgres and SQL Server use WHERE in index; syntax similar.
• Cost — same as regular unique index; smaller because it excludes NULL rows.

SQL
Topic — indexing
SQL indexing drills

Practice →

Practice →

5. IS DISTINCT FROM + COALESCE + NULLIF + dialect matrix

is distinct from, sql coalesce, sql nullif — the three NULL-handling functions every engineer must know, and the 8-engine dialect matrix

The mental model in one line: IS DISTINCT FROM is null-safe inequality (treats NULL as equal only to NULL), COALESCE(a, b, c, ...) returns the first non-NULL argument (universal since ANSI SQL), and NULLIF(a, b) returns NULL if a=b else a (used to guard against division by zero and other "sentinel" patterns) — these three cover 95% of daily NULL manipulation across all eight major SQL engines.

Slot 1 — IS DISTINCT FROM / IS NOT DISTINCT FROM.

• a IS DISTINCT FROM b — TRUE if a and b differ (including NULL vs non-NULL). FALSE if same (including both NULL).
• a IS NOT DISTINCT FROM b — inverse; TRUE if same, FALSE if differ.
• Semantics. Null-safe inequality / equality.
• ANSI SQL. Standard since 2003.
• Support. Postgres, Oracle, DuckDB, Snowflake, Databricks — native. MySQL 8.0.16+ native, or use <=> operator. SQL Server — no native; workaround via CASE.
• Use case. Change detection, MERGE conditional UPDATE, null-safe compare in filters.

Slot 2 — COALESCE.

• COALESCE(a, b, c, ...) — returns first non-NULL argument. If all NULL, returns NULL.
• Universal. All engines support since SQL:1999.
• Type coercion. Arguments should be compatible types; result type is the common supertype.
• Short-circuit. Evaluates arguments left-to-right, stops at first non-NULL.
• Common uses. Default values (COALESCE(discount, 0)), fallback columns (COALESCE(nickname, first_name, 'user')), null-safe arithmetic.

Slot 3 — NULLIF.

• NULLIF(a, b) — returns NULL if a=b, else a.
• Universal. SQL:1999 standard.
• Use case — divide by zero. SUM(x) / NULLIF(SUM(y), 0) — returns NULL instead of error when denominator is 0.
• Use case — treat sentinel as NULL. NULLIF(col, '') — converts empty string to NULL.
• Combines with COALESCE. COALESCE(NULLIF(col, ''), 'default') — treats empty string as missing, provides default.

Slot 4 — engine-specific NULL functions.

• ISNULL(a, b) (SQL Server, MySQL). SQL Server: return a if not NULL else b (like COALESCE with 2 args). MySQL: return 1 if NULL else 0. Different semantics — confusing.
• IFNULL(a, b) (MySQL, SQLite). Return a if not NULL else b. Same as COALESCE(a, b).
• NVL(a, b) (Oracle). Return a if not NULL else b. Same as COALESCE(a, b).
• NVL2(a, b, c) (Oracle). Return b if a is not NULL else c. Rare.
• Prefer COALESCE for portability.

Slot 5 — CASE WHEN as fallback.

• CASE WHEN a IS NULL THEN default ELSE a END — long form of COALESCE(a, default).
• Portable. Works everywhere.
• When to use. When logic is more complex than just "first non-NULL."
• Verbose compared to COALESCE.

Slot 6 — the 8-engine dialect matrix.

Slot 7 — MySQL <=> operator.

• <=> — null-safe equal. NULL <=> NULL returns 1 (TRUE); NULL <=> 5 returns 0 (FALSE).
• Common in MySQL. Predates IS NOT DISTINCT FROM in MySQL.
• Semantics. Same as IS NOT DISTINCT FROM.
• Best practice. Prefer IS NOT DISTINCT FROM in new MySQL 8+ code; <=> for legacy compatibility.

Slot 8 — SQL Server null-safe compare workaround.

• No IS DISTINCT FROM. Requires CASE.
• Pattern. CASE WHEN a IS NULL AND b IS NULL THEN 0 WHEN a IS NULL OR b IS NULL THEN 1 WHEN a = b THEN 0 ELSE 1 END. Returns 0 for same, 1 for different.
• Verbose. But portable.

Slot 9 — combining with GROUPING SETS.

• From Blog262. GROUPING() distinguishes subtotal-NULL from data-NULL.
• Format labels. CASE WHEN GROUPING(col) = 1 THEN 'All' ELSE COALESCE(col, 'Unknown') END.
• Both NULLs handled. Subtotal-NULL becomes "All"; data-NULL becomes "Unknown".

Slot 10 — patterns for null-safe columns.

• Default at column level. col INT DEFAULT 0 NOT NULL — never NULL.
• Application default. Insert with COALESCE.
• Query-time default. COALESCE(col, 0) in every SELECT. Repetitive.
• View or computed column. col_safe AS COALESCE(col, 0). Materialized version of the pattern.

Common beginner mistakes

• Using ISNULL(a, b) on MySQL expecting SQL Server semantics.
• Not knowing which is COALESCE vs NULLIF.
• Writing CASE WHEN a IS NULL THEN NULL ELSE a END — equivalent to just a.
• Using CASE when COALESCE suffices — verbose without benefit.
• Missing NULLIF for divide-by-zero.

Worked example — the divide-by-zero guard with NULLIF

Detailed explanation. Compute click-through rate; guard against zero impressions.

Code.

SELECT
  campaign_id,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions,
  ROUND(
    100.0 * SUM(clicks) / NULLIF(SUM(impressions), 0),
    2
  ) AS ctr_pct
FROM ad_events
GROUP BY campaign_id;

Step-by-step explanation.

1. Compute CTR = clicks / impressions.
2. If impressions = 0, dividing by 0 would error out.
3. NULLIF(SUM(impressions), 0) returns NULL if 0, else the sum.
4. x / NULL returns NULL (not error).
5. CTR for zero-impression campaigns comes back as NULL.

Output. CTR computed; zero-impression campaigns show NULL.

Rule of thumb. Every SQL division should use NULLIF(denom, 0) to guard against divide-by-zero.

Worked example — COALESCE fallback for user display name

Code.

SELECT
  id,
  COALESCE(nickname, first_name, last_name, 'User ' || id) AS display_name
FROM users;

Step-by-step explanation.

1. Try nickname first; if NULL, first_name; if NULL, last_name; if NULL, generate default.
2. Left-to-right short-circuit.
3. Universal across engines.

Output. Always non-NULL display_name.

Rule of thumb. COALESCE is the fallback pattern. Chain fields in preference order.

Worked example — IS DISTINCT FROM in MERGE conditional UPDATE

Detailed explanation. From Blog261 — update only when values actually differ (null-safe).

Code.

MERGE INTO dim_customer t
USING stg_customer s ON t.id = s.id
WHEN MATCHED AND (
     t.name    IS DISTINCT FROM s.name
  OR t.email   IS DISTINCT FROM s.email
  OR t.country IS DISTINCT FROM s.country
) THEN UPDATE SET
  name = s.name, email = s.email, country = s.country, updated_at = NOW();

Step-by-step explanation.

1. Only update when any column value differs.
2. IS DISTINCT FROM is null-safe — NULL → 'X' is a change; NULL → NULL is not.
3. Prevents unnecessary micro-partition rewrites.
4. Same pattern works everywhere IS DISTINCT FROM is supported.

Output. Only changed rows updated.

Rule of thumb. MERGE / UPDATE conditional guards should use IS DISTINCT FROM for null-safe compare.

sql coalesce interview question on report formatting

A senior interviewer asks: "Build a customer report showing preferred email or phone, with '(none)' if both are NULL. Handle empty strings as NULL too."

Solution Using COALESCE + NULLIF for empty-string handling

SELECT
  id,
  name,
  COALESCE(
    NULLIF(TRIM(email), ''),
    NULLIF(TRIM(phone), ''),
    '(none)'
  ) AS contact
FROM customers;

Step-by-step trace.

Output: Always non-NULL contact string.

Why this works — concept by concept:

• TRIM removes leading/trailing whitespace — empty string with whitespace becomes truly empty.
• NULLIF('') converts empty to NULL — matches missing-data intent.
• COALESCE picks first non-NULL — falls through email → phone → '(none)'.
• Chain of transformations — TRIM → NULLIF → COALESCE. Each step handles one class of missing.
• Portable — works on all 8 engines.

SQL
Topic — SQL
SQL practice library

Practice →

SQL
Topic — aggregation
SQL aggregation drills

Practice →

Cheat sheet — NULL semantics recipe list

• NULL is not a value. It's the absence. Every op propagates.
• NULL = NULL is UNKNOWN. Not TRUE.
• IS NULL / IS NOT NULL. Only reliable NULL tests.
• WHERE drops UNKNOWN. Treated as not-TRUE.
• CHECK allows UNKNOWN. Treated as not-FALSE. Add NOT NULL for real enforcement.
• COUNT(*) counts NULL rows. COUNT(col) skips them.
• SUM / AVG / MIN / MAX skip NULLs. AVG divides by non-NULL count.
• GROUP BY treats NULL as equal. All NULLs form one group.
• DISTINCT includes NULL once.
• ORDER BY NULL default varies. Always spell NULLS FIRST / NULLS LAST.
• INNER JOIN drops NULL-key rows. LEFT JOIN keeps them.
• NOT IN (subquery with NULL) = 0 rows. Use NOT EXISTS.
• UNIQUE + NULL varies per engine. Postgres/Oracle allow many; SQL Server allows one.
• PRIMARY KEY disallows NULL. Always.
• FK NULL means "no reference." Allowed unless NOT NULL.
• IS DISTINCT FROM. Null-safe inequality. TRUE if differ (including NULL/non-NULL).
• COALESCE(a, b, c). First non-NULL.
• NULLIF(a, b). NULL if equal.
• Divide by NULLIF(denom, 0). Divide-by-zero guard.
• MERGE conditional UPDATE with IS DISTINCT FROM. Skip unchanged rows.
• CDC deletes = NULL after-value. Handle in MERGE.
• Empty string vs NULL. Different in most engines (Oracle treats them the same). NULLIF(x, '') normalises.
• CASE WHEN can express any NULL logic. Verbose but portable.
• <=> (MySQL). Null-safe equal. Same as IS NOT DISTINCT FROM.
• ISNULL / IFNULL / NVL. Engine-specific 2-arg COALESCE alternatives.
• NVL2(a, b, c) (Oracle). Return b if a not NULL else c.
• Aggregation returns NULL for all-NULL group. Use COALESCE(SUM, 0) for safe totals.
• HAVING drops NULL aggregate. Use IS NULL or IS DISTINCT FROM in HAVING.
• JSON NULL vs SQL NULL. JSON null is distinct from missing key.
• Query planner uses IS NULL as sargable. Index seek possible on IS NULL.

Frequently asked questions

Why does NULL = NULL return UNKNOWN instead of TRUE?

Because in SQL, NULL represents "the value is unknown" — and if you compare two unknowns, you can't know whether they're the same. If A and B are both unknown, they might be equal or they might not, so the correct answer is UNKNOWN, not TRUE. This is the foundation of three-valued logic (TVL) in SQL — TRUE, FALSE, and UNKNOWN. Every operator involving NULL propagates UNKNOWN unless the operator explicitly handles NULL specially (IS NULL, IS DISTINCT FROM). Consequences — NULL = 'X', NULL != 'X', NULL > 0, NULL < 100 all return UNKNOWN. WHERE clauses filter out UNKNOWN (treating them as not-TRUE), so any row with NULL in the compared column is silently dropped. This is source of the classic "my report is missing 15% of rows" bug. Fix — use IS NULL / IS NOT NULL / IS DISTINCT FROM explicitly at every boundary.

What's the difference between COUNT(*) and COUNT(col)?

COUNT(*) counts every row in the group, including rows with NULL in every column. COUNT(col) counts only rows where col is NOT NULL. Consequence — on a table with 100 rows where 15 have NULL customer_id, COUNT(*) returns 100 and COUNT(customer_id) returns 85. Related: COUNT(DISTINCT col) counts distinct non-NULL values. Common bug: using COUNT(col) when you meant row count. If col has NULLs, you undercount. Rule of thumb: use COUNT(*) for row count; use COUNT(col) only when you specifically want non-NULL count.

Why does my CHECK constraint accept NULL values?

Because CHECK constraints in SQL are defined to reject only rows where the check condition is explicitly FALSE. If the condition evaluates to UNKNOWN (which happens whenever NULL is involved), the CHECK passes. Example: CHECK (quantity > 0) accepts NULL because NULL > 0 is UNKNOWN, not FALSE. This is the source of the classic "positive quantity constraint doesn't prevent NULL" bug. Fixes: (1) add NOT NULL to the column definition, (2) rewrite the CHECK to include an explicit NULL test: CHECK (quantity IS NOT NULL AND quantity > 0), (3) accept that NULL is legitimate (represents "unknown quantity") and enforce elsewhere. This CHECK/WHERE asymmetry — WHERE drops UNKNOWN, CHECK allows UNKNOWN — is one of the most common SQL surprises.

How do I handle NULL in ORDER BY consistently across engines?

Always spell out NULLS FIRST or NULLS LAST explicitly. Never rely on defaults — they vary by engine (Postgres/Oracle default NULLS LAST for ASC and NULLS FIRST for DESC; MySQL, SQL Server, BigQuery, Snowflake vary). Supported syntax: ORDER BY col ASC NULLS LAST, ORDER BY col DESC NULLS FIRST, etc. Supported on Postgres, Oracle, MySQL 8+, BigQuery, Snowflake, Databricks, DuckDB. Not on SQL Server — use ORDER BY (CASE WHEN col IS NULL THEN 1 ELSE 0 END), col as workaround. Rule of thumb: every ORDER BY on a nullable column should have explicit NULLS FIRST or NULLS LAST. Makes the intent clear and portable.

What does IS DISTINCT FROM do?

a IS DISTINCT FROM b is null-safe inequality — returns TRUE if a and b differ (including one being NULL and the other not), FALSE if they're the same (including both being NULL). Contrast with a != b: for NULL vs anything, != returns UNKNOWN, and WHERE drops UNKNOWN. IS DISTINCT FROM returns TRUE or FALSE, never UNKNOWN. Uses: (1) change-detection queries (WHERE new IS DISTINCT FROM old), (2) MERGE conditional UPDATE (only update when values actually changed), (3) null-safe join conditions (ON t1.x IS NOT DISTINCT FROM t2.x). Supported on Postgres, Oracle, MySQL 8.0.16+, Snowflake, BigQuery, Databricks, DuckDB — native ANSI syntax. SQL Server requires CASE workaround. MySQL also supports the <=> (null-safe equal) shorthand.

How do I prevent NULL from breaking my UNIQUE constraint?

Behavior varies by engine. Postgres, Oracle, MySQL — UNIQUE constraints allow multiple NULLs (treating them as "distinct" for uniqueness purposes). If you want "at most one NULL," add NOT NULL to the column or create a partial/filtered unique index: CREATE UNIQUE INDEX ON t (col) WHERE col IS NULL — enforces at most one NULL row. SQL Server — allows only one NULL by default; that's often surprising. Filtered index gives you partial uniqueness: CREATE UNIQUE INDEX ON t (col) WHERE col IS NOT NULL — allows multiple NULLs while enforcing uniqueness on non-NULL. Snowflake, BigQuery — UNIQUE is informational only, not enforced. Rule of thumb: know your engine's default. Partial/filtered unique indexes are the escape hatch for either direction.

Practice on PipeCode

• Drill the SQL practice library → — 450+ DE-focused questions covering NULL semantics, three-valued logic, IS DISTINCT FROM, COALESCE, NULLIF, and every adjacent pattern.
• Sharpen SQL aggregation drills → for COUNT(*) vs COUNT(col), NULL-safe AVG, and HAVING with NULL.
• Layer SQL join drills → — INNER vs LEFT JOIN semantics on nullable columns, anti-joins, and NULL-safe joins.
• Warm up with SQL optimization drills → — index behavior on nullable columns, partial index design.
• Layer SQL indexing drills → — UNIQUE + NULL across engines, partial and filtered indexes.
• For the broader SQL interview surface, take the SQL for Data Engineering course →.

Pipecode.ai is Leetcode for Data Engineering — every `sql null three valued logic` recipe above ships with hands-on practice rooms where you type `WHERE customer_id IS DISTINCT FROM 42` on a live Postgres, spot the NULL-drop bug in a naive `!= 42` filter, learn why `CHECK (quantity > 0)` doesn't prevent NULL, benchmark `COALESCE(SUM(x), 0)` against a NULL group, and finally build the safe report with `NULLIF(TRIM(email), '')` handling empty-string sentinels — the exact NULL-semantics fluency that senior DE interviews probe. PipeCode pairs every NULL-handling concept with 450+ DE-focused problems and a real-time scoring engine, so you never have to wonder whether your `is null` / `is distinct from` / `coalesce` / `nullif` answer holds up under a senior interviewer's depth probes.

Practice SQL now →
Aggregation drills →