--- name: null-handling-in-sql description: > Write SQL that handles NULL correctly, so queries do not silently drop rows, return wrong counts or break on three-valued logic, across PostgreSQL, MySQL and MariaDB, SQLite and SQL Server. Covers IS NULL versus = NULL, the NOT IN NULL trap, NULL-safe comparison (IS DISTINCT FROM, <=>), COALESCE, NULLIF, IFNULL and ISNULL, how aggregates and COUNT treat NULL, and NULLs in GROUP BY, DISTINCT, ORDER BY, joins and UNIQUE. Use it when someone says "my query returns no rows and I do not know why", "NOT IN returns nothing", "= NULL does not work", "COUNT is wrong", "the average looks off", "how do I replace NULL with 0", "treat NULLs as equal", "why is this row missing after the join", or "NULLS FIRST / NULLS LAST". Do NOT use it for conditional aggregation and pivoting with CASE (see conditional-aggregation), join logic in general (see sql-joins), window functions (see window-functions), or index design (see database-indexing). license: Free to use and adapt under CC BY 4.0, with credit to Afeez Adebayo. Link afeezadebayo.com, https://www.linkedin.com/in/afeez-adebayo/ and this skill's page at https://afeezadebayo.com/skills-md/data/sql-querying/null-handling-in-sql/. --- # NULL handling in SQL ## When to use this, and when not to Use this skill when a query behaves strangely around missing values: it returns no rows for no obvious reason, a `NOT IN` comes back empty, a filter on inequality drops rows you expected, a `COUNT` or average disagrees with a hand check, or you need to replace, compare or sort NULLs. The common thread is that NULL is not a value. It means "unknown" or "not applicable", and SQL evaluates it with three-valued logic, where a comparison can be true, false, or unknown. Most NULL bugs are a confusion between false and unknown. Do not use this skill for conditional aggregation and pivoting with `CASE` inside aggregates (that is `conditional-aggregation`, which relies on the aggregate NULL rules this skill explains), for join types and matching logic in general (`sql-joins`), for window functions (`window-functions`), or for index design on nullable columns (`database-indexing`). The defining feature here is reasoning about the truth value of expressions that touch NULL, and choosing the operator or function that gives the result you meant. The core idea: a comparison with NULL, such as `x = NULL`, is neither true nor false, it is unknown. `WHERE`, `HAVING`, `JOIN ... ON` and `CASE WHEN` keep a row only when the condition is true, so unknown behaves like false and the row is dropped. `CHECK` constraints do the opposite and accept unknown. Get that one asymmetry right and most NULL surprises disappear. ## Inputs the agent must have before starting 1. The SQL engine and version. NULL-safe operators, `NULLS FIRST`/`LAST`, and the NULL-replacement functions differ by engine (see the appendix). 2. Which columns in play are nullable, and what a NULL means in each: truly unknown, not applicable, or "none". The meaning decides whether you keep it, exclude it or replace it. 3. Whether any predicate uses `NOT IN`, `<>`/`!=`, or `NOT (...)` against a column or subquery that can be NULL. These are where rows vanish. 4. Whether a NULL should be treated as equal to another NULL for this task (deduplication, change detection, grouping) or as distinct (ordinary comparison). 5. For aggregates and averages: whether a missing value should count as zero or be excluded. The two give different numbers. If you cannot tell what a NULL means in a column, ask. Replacing an "unknown" with `0` or `''` can turn a correct "we do not know" into a confident wrong answer, and it hides data-quality problems you may need to fix at the source. ## The workflow, step by step ### 1. Decide what NULL means in each column before touching it Separate the three cases. "Unknown" (a survey answer not yet given) should usually stay NULL and be excluded from calculations. "Not applicable" (a `cancelled_at` on an active order) is normal and should not be coalesced into a fake value. "None" that was stored as NULL rather than `0` or `''` is a modelling choice you can normalise. Decision rule: only replace a NULL with a concrete value when the task's maths or display truly needs that value, and never when the NULL carries information you will later rely on. ### 2. Test for NULL with IS NULL, never with equality `x = NULL`, `x <> NULL`, `x < NULL` all evaluate to unknown, so a `WHERE x = NULL` returns no rows and a `WHERE x <> 'a'` silently drops every row where `x` is NULL. Use `IS NULL` and `IS NOT NULL`, which always return true or false. ```sql -- Wrong: returns no rows SELECT * FROM person WHERE phone = NULL; -- Right SELECT * FROM person WHERE phone IS NULL; ``` MySQL documents this directly: "the NULL value is never true in comparison to any other value, even NULL" (MySQL 8.0 manual, B.3.4.3). The same holds in every engine tested. ### 3. Watch the inequality filter that drops NULL rows A filter like `WHERE status <> 'closed'` returns only rows whose status is a known value other than `'closed'`. Rows with `status` NULL are excluded, because `NULL <> 'closed'` is unknown. If you want those rows too, say so: ```sql WHERE status <> 'closed' OR status IS NULL ``` This is the most common "my query is missing rows" cause after an outer join or an optional column. ### 4. Fix the NOT IN NULL trap If the list or subquery after `NOT IN` can contain a single NULL, the whole predicate can never be true, so the query returns zero rows and raises no error. `1 NOT IN (NULL, 2)` is unknown, not true (modern-sql.com, three-valued logic). `x IN (list)` is true on a match, false when absent and the list has no NULL, and unknown when absent and the list contains a NULL; negating unknown stays unknown, so the row is dropped (SQLServerCentral, "NOT IN condition"). Decision rule: never use `NOT IN` with a subquery over a nullable column. Default to `NOT EXISTS`, which is correlated and never returns unknown: ```sql -- Fragile: empty result if any gl_claims.claim_sk is NULL SELECT claim_sk FROM dim_claims WHERE claim_sk NOT IN (SELECT claim_sk FROM gl_claims); -- Robust SELECT claim_sk FROM dim_claims d WHERE NOT EXISTS ( SELECT 1 FROM gl_claims g WHERE g.claim_sk = d.claim_sk ); ``` If you must keep `NOT IN`, filter NULLs out of the subquery with `WHERE claim_sk IS NOT NULL`, or use a left anti-join (`LEFT JOIN ... WHERE right.key IS NULL`). `NOT EXISTS` is the safest default and usually plans as well or better. See `subqueries-and-ctes` for the subquery forms. ### 5. Compare NULLs as equal when the task needs it Ordinary comparison treats two NULLs as different. For deduplication, change detection and "has this value actually changed" checks, you often want two NULLs to count as equal. Use a NULL-safe comparison: - PostgreSQL and SQLite: `a IS NOT DISTINCT FROM b` is true when both are NULL or both are equal; `a IS DISTINCT FROM b` is its negation. - MySQL and MariaDB: the `<=>` operator, so `a <=> b` returns 1 when both are NULL. - SQLite also lets you use `a IS b` as a NULL-safe equality. - SQL Server: `IS [NOT] DISTINCT FROM` from SQL Server 2022; on older versions emulate it (see the appendix). ```sql -- Only update when the incoming value really differs, NULLs included UPDATE t SET note = :new WHERE id = :id AND note IS DISTINCT FROM :new; ``` ### 6. Replace NULL for output with the right function To show a fallback instead of NULL, use the standard `COALESCE(a, b, ...)`, which returns the first non-null argument and evaluates only the arguments it needs (PostgreSQL docs, conditional expressions). To turn a value into NULL, or to guard division, use `NULLIF(a, b)`, which returns NULL when `a = b`. ```sql SELECT COALESCE(nickname, first_name, 'Anonymous') AS display_name, total / NULLIF(orders, 0) AS avg_order_value -- no divide-by-zero FROM customer; ``` `NULLIF(orders, 0)` makes a zero denominator NULL, so the division yields NULL instead of erroring. Prefer `COALESCE` over the engine-specific `IFNULL` (MySQL, SQLite) or `ISNULL` (SQL Server) for portability. `COALESCE` and `ISNULL` are not interchangeable in SQL Server: they differ in result data type, nullability and evaluation count (see the appendix). ### 7. Know how aggregates treat NULL Every common aggregate except `COUNT(*)` ignores NULL inputs. `COUNT(*)` counts rows; `COUNT(col)` counts only rows where `col` is non-null (PostgreSQL docs, aggregate expressions; MySQL B.3.4.3). This drives three regular bugs: - `AVG(col)` divides the sum of non-null values by the count of non-null values. If a missing value should count as zero, the average is wrong unless you coalesce first: `AVG(COALESCE(col, 0))`. If missing truly means "exclude", plain `AVG(col)` is right. Decide which, every time. - `SUM(col)` over zero non-null rows returns NULL, not `0`. Wrap it: `COALESCE(SUM(col), 0)`. In SQLite, `total(col)` returns `0.0` in that case while `sum(col)` returns NULL (SQLite aggregate docs). - `COUNT(col)` and `COUNT(*)` differ whenever `col` has NULLs, so pick the one you mean. ### 8. Handle NULLs in GROUP BY, DISTINCT and UNION For `DISTINCT`, `GROUP BY` and set operators, all NULLs are regarded as equal, so they fold into a single group or a single distinct row (MySQL B.3.4.3; SQLite aligns with the PostgreSQL and Oracle model). This is the opposite of ordinary comparison, and it is usually what you want for a grouped report, but note that a `GROUP BY nullable_col` produces one NULL bucket that mixes all the "unknown" rows together. ### 9. Control where NULLs sort In `ORDER BY`, NULLs sort to one end, and the default differs by engine. MySQL sorts NULLs first on `ASC` and last on `DESC`. To be explicit and portable where supported, use `NULLS FIRST` or `NULLS LAST` (PostgreSQL, SQLite 3.30+, Oracle). On engines without it (MySQL, older SQL Server), emulate with a leading sort key (see the appendix). Decision rule: if NULL placement matters to the reader, state it; do not rely on the default. ### 10. Remember that join keys never match on NULL A NULL join key matches nothing, not even another NULL, because the `ON` condition is a comparison and `NULL = NULL` is unknown. Rows with a NULL foreign key will not join. Separately, an outer join introduces NULLs for unmatched rows, so a NULL in an outer-join result can mean either "stored NULL" or "no match". When that distinction matters, test a `NOT NULL` column from the right table (`WHERE r.id IS NULL` is the anti-join; a non-key column cannot tell the two apart). Join mechanics live in `sql-joins`. ### 11. Verify Check that every NULL test uses `IS NULL`/`IS NOT NULL`, that no `NOT IN` sits over a nullable subquery, that negated filters (`<>`, `NOT`) keep or drop NULL rows on purpose, that `SUM` results that feed further maths are coalesced, that `AVG` either excludes or zero-fills missing values deliberately, and that `ORDER BY` NULL placement is explicit where it matters. Run the query once with and once without the NULL rows present to confirm the count is what you intend. ## Worked example, end to end A team wants customers who have never placed an order, with a display name and each customer's average order value. The first attempt: ```sql SELECT COALESCE(nickname, email) AS who, AVG(o.amount) AS avg_value FROM customer c LEFT JOIN orders o ON o.customer_id = c.id WHERE c.id NOT IN (SELECT customer_id FROM orders) GROUP BY c.id, nickname, email; ``` It returns nothing, and no error. The cause is step 4: `orders.customer_id` is nullable (some orders are guest orders with no customer), so the subquery contains a NULL, and `NOT IN` can never be true. The fix is `NOT EXISTS`: ```sql SELECT COALESCE(c.nickname, c.email) AS who FROM customer c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id ); ``` The `AVG` and the join are dropped, because customers with no orders have nothing to average. For the separate question "average order value per customer, counting customers with no orders as zero", the NULL from the outer join must be made explicit: ```sql SELECT c.id, COALESCE(c.nickname, c.email) AS who, COALESCE(AVG(o.amount), 0) AS avg_value, -- NULL avg for no orders becomes 0 COUNT(o.id) AS order_count -- COUNT(o.id), not COUNT(*) FROM customer c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id, c.nickname, c.email ORDER BY avg_value DESC NULLS LAST; ``` `COUNT(o.id)` returns `0` for a customer with no orders, while `COUNT(*)` would return `1` because the outer join leaves one all-NULL row. `COALESCE(AVG(...), 0)` turns the empty average into zero on purpose, and `NULLS LAST` keeps the placement defined. Each NULL decision here was made deliberately, which is the whole point. ## Variations you will meet in practice - **Divide-by-zero.** Guard every division whose denominator can be zero with `NULLIF`: `numerator / NULLIF(denominator, 0)`. The result is NULL rather than an error, which you then coalesce if you need a number. - **Sentinel values hiding as data.** Legacy data often stores `-1`, `9999` or `'N/A'` where NULL belongs. Convert on read with `NULLIF(col, -1)` so the rest of your logic sees a real NULL, and plan a one-off clean-up (`data-cleaning-preparation`, once published). - **Composite-key comparison.** To compare multi-column keys where any part can be NULL, compare each part NULL-safely (`a1 IS NOT DISTINCT FROM b1 AND a2 IS NOT DISTINCT FROM b2`), not with a plain row-value comparison, which goes unknown on a NULL. - **CHECK constraints accept unknown.** `CHECK (a + b <= 10)` passes a row where `a` or `b` is NULL, because the condition is unknown, not false (modern-sql.com). If the rule must hold, add `AND a IS NOT NULL AND b IS NOT NULL`, or make the columns `NOT NULL`. - **Empty string is not NULL.** `''` and `0` are values, not NULL (MySQL B.3.4.3). A `NOT NULL` column can still hold an empty string, so validate both if "no value" matters. ## Templates you can use verbatim ```sql -- Test for missing WHERE col IS NULL WHERE col IS NOT NULL -- Keep NULL rows under a negated filter WHERE status <> 'closed' OR status IS NULL -- Anti-join without the NOT IN trap SELECT a.* FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.key = a.key); -- NULL-safe equality (pick per engine) WHERE a IS NOT DISTINCT FROM b -- PostgreSQL, SQLite, SQL Server 2022+ WHERE a <=> b -- MySQL / MariaDB -- Replace / guard SELECT COALESCE(col, fallback) AS shown, num / NULLIF(den, 0) AS safe_ratio; -- Aggregates with deliberate NULL handling SELECT COALESCE(SUM(amount), 0) AS total, -- empty / all-NULL sum -> 0 AVG(amount) AS avg_excluding_missing, AVG(COALESCE(amount, 0)) AS avg_counting_missing_as_zero, COUNT(amount) AS rows_with_amount, COUNT(*) AS all_rows FROM t; -- Defined NULL sort ORDER BY sort_col DESC NULLS LAST; -- PG / SQLite 3.30+ / Oracle ``` ## Failure modes and anti-patterns 1. **Testing NULL with `=` or `<>`.** Spot it when `WHERE col = NULL` returns nothing, or a `WHERE col <> 'x'` drops more rows than expected. Fix: `IS NULL` / `IS NOT NULL`, and add `OR col IS NULL` to negated filters when you want the NULL rows. 2. **`NOT IN` over a nullable subquery.** Spot it when the query returns zero rows and no error, and works the moment you add `IS NOT NULL` to the subquery. Fix: use `NOT EXISTS` by default. 3. **Assuming NULL counts as zero in aggregates.** Spot it when an average or sum is higher or lower than a hand check, or a `SUM` over no rows comes back NULL and breaks downstream maths. Fix: decide per column: `AVG(COALESCE(col,0))` to count missing as zero, plain `AVG(col)` to exclude, and `COALESCE(SUM(col),0)` when you need a number. 4. **`COUNT(*)` where you meant `COUNT(col)`.** Spot it when a count after an outer join is one too many, or includes rows with no real value. Fix: `COUNT(col)` counts non-null values; `COUNT(*)` counts rows. 5. **Unplanned NULL sort position or a `GROUP BY` NULL bucket.** Spot it when NULLs appear at the wrong end of a sorted list, or an unexpected NULL group appears in a report. Fix: set `NULLS FIRST`/`LAST` explicitly, and filter or label the NULL group when grouping on a nullable column. ## Quality bar - Every NULL test uses `IS NULL`/`IS NOT NULL`; no `= NULL` or `<> NULL` anywhere. - No `NOT IN` sits over a column or subquery that can be NULL; anti-joins use `NOT EXISTS` or a left anti-join. - Negated filters (`<>`, `NOT`) keep or drop NULL rows on purpose, stated in the query. - Every `AVG` either excludes missing values or zero-fills them deliberately, and every `SUM` that feeds further maths is coalesced. - `COUNT(*)` versus `COUNT(col)` is the one you meant, and NULL sort placement is explicit where a reader would notice. - NULL-safe comparison is used wherever two NULLs should count as equal (deduplication, change detection). ## Data handling and safety NULL handling is mostly read-side reasoning, but two cautions apply. First, do not mask data-quality problems: blanket `COALESCE(col, 0)` or converting every NULL to `''` can hide that a pipeline is dropping values, so prefer to surface and fix the source. Second, any statement that rewrites NULLs in place (`UPDATE t SET col = COALESCE(col, :default)`, or backfilling a newly `NOT NULL` column) is a bulk, irreversible write: show the affected row count, take a backup, and get explicit human confirmation before running it, exactly as for any mass update. When the data includes personal information, treat a NULL as "unknown" rather than inventing a placeholder that could later be read as a real value, and never log row contents while debugging NULL issues. ## Appendix: engine-specific notes (verify before relying on these) - **PostgreSQL**: `IS [NOT] DISTINCT FROM` for NULL-safe comparison; `COALESCE`, `NULLIF`, `GREATEST`/`LEAST` (which ignore NULL arguments, a deliberate deviation from the SQL standard, per the PostgreSQL docs); `NULLS FIRST`/`NULLS LAST` in `ORDER BY`; `FILTER (WHERE ...)` on aggregates. `count(*)` counts rows, `count(col)` skips NULLs. - **MySQL and MariaDB**: `<=>` is the NULL-safe equal (`a <=> b` is 1 when both are NULL); `IFNULL(a,b)` and `COALESCE(a,...)` both exist; no `NULLS FIRST`/`LAST`, so emulate with a leading key, for example `ORDER BY (col IS NULL), col` to force NULLs last on `ASC`. NULLs sort first on `ASC`, last on `DESC` by default. No `FILTER` clause: use `CASE` inside the aggregate (see `conditional-aggregation`). - **SQLite**: `a IS b` and `a IS NOT b` act as NULL-safe equality; `IS [NOT] DISTINCT FROM` is also accepted; `IFNULL`, `COALESCE`, `NULLIF`; `total(x)` returns `0.0` on all-NULL input where `sum(x)` returns NULL; `NULLS FIRST`/`LAST` from 3.30; `FILTER` is supported on aggregates. NULLs are indistinct for `SELECT DISTINCT` and `UNION`, matching PostgreSQL and Oracle. - **SQL Server**: `ISNULL(a,b)` takes two arguments and takes the type and non-nullability of the first; `COALESCE` is standard, variadic, follows `CASE` type rules and is treated as nullable, and may evaluate an input more than once (Microsoft Learn, COALESCE). `IS [NOT] DISTINCT FROM` from SQL Server 2022; on earlier versions emulate with `((a = b) OR (a IS NULL AND b IS NULL))` or `EXCEPT`. NULL behaviour under `=` depends on `SET ANSI_NULLS` (keep it `ON`). No `FILTER` clause; use `CASE`. ## Sources - MySQL 8.0 Reference Manual, "Working with NULL Values" and B.3.4.3 "Problems with NULL Values": `expr = NULL` is never true so use `IS NULL`; aggregates and `MIN`/`MAX`/`SUM` ignore NULL while `COUNT(*)` counts rows and `COUNT(col)` counts non-null values; `DISTINCT`/`GROUP BY`/`ORDER BY` treat all NULLs as equal; NULLs sort first on `ASC`, last on `DESC`; empty string and `0` are values, not NULL. Verified 30 October 2026. - PostgreSQL documentation, "Conditional Expressions" (CASE, COALESCE, NULLIF, GREATEST/LEAST) and "Aggregate Expressions" (FILTER, aggregates ignore NULL, `count(*)` versus `count(f1)`): COALESCE returns the first non-null and short-circuits; NULLIF returns NULL when the two are equal; GREATEST/LEAST ignore NULL arguments. Verified 30 October 2026. - SQLite documentation, "NULL Handling in SQLite Versus Other Database Engines" and "Built-in Aggregate Functions": cross-engine behaviour (NULLs indistinct for DISTINCT/UNION, distinct in UNIQUE, `null OR true` is true); `sum()` returns NULL and `total()` returns `0.0` on all-NULL input; `count(X)` counts non-null values; FILTER supported. Verified 30 October 2026. - modern-sql.com (Markus Winand), "Three-Valued Logic": comparisons with NULL are unknown; WHERE/HAVING/ON require true, CHECK accepts unknown; `1 NOT IN (NULL, 2)` is unknown so a `NOT IN` containing NULL is never true; use `NOT EXISTS` or filter NULLs; `IS [NOT] DISTINCT FROM` for NULL-safe comparison. Verified 30 October 2026. - SQLServerCentral, "NOT IN condition": why a `NOT IN` subquery containing NULL returns no rows (IN gives UNKNOWN, NOT UNKNOWN is not TRUE), and the fixes (NOT EXISTS, filter NULLs, left anti-join). Verified 30 October 2026. - Microsoft Learn, "COALESCE (Transact-SQL)": the five documented differences between COALESCE and ISNULL (evaluation count, data-type determination, result nullability, typed-NULL validation, argument count) and the `SET ANSI_NULLS` dependency. Verified 30 October 2026.