SQL NULL traps: = NULL, NOT IN, and sort order

Posted by Kyle Hankinson August 7, 2026

---

A query that returns zero rows with zero errors is harder to debug than one that fails loudly, and NULL is behind more of those silent empties than everything else combined. The root cause is always the same: SQL comparisons involving NULL do not evaluate to true or false but to a third value, UNKNOWN, and a WHERE clause keeps only rows where the condition is TRUE. UNKNOWN rows are dropped without comment.

That one rule produces three distinct traps. All of the MySQL, PostgreSQL, and SQLite behavior below was run against MySQL 8.4, PostgreSQL 16, and SQLite 3.50.6; the SQL Server behavior is cited from Microsoft's documentation, linked where used.

Two tables are enough to demonstrate everything:

CREATE TABLE customers (id int, name varchar(20));
INSERT INTO customers VALUES (1, 'alice'), (2, 'bob'), (3, 'carol');

CREATE TABLE orders (customer_id int);
INSERT INTO orders VALUES (1), (NULL);   -- one order has no customer

Trap 1: WHERE col = NULL matches nothing

The natural way to find the orphaned order reads fine and returns nothing:

SELECT count(*) FROM orders WHERE customer_id = NULL;   -- 0
SELECT count(*) FROM orders WHERE customer_id IS NULL;  -- 1

Identical results on MySQL, PostgreSQL, and SQLite: the = version finds zero rows even though a NULL row is sitting right there. NULL means "unknown", and asking whether an unknown value equals another unknown value can only be answered "unknown", so the comparison never becomes TRUE for any row. SQL Server follows the same logic under its default settings; Microsoft's NULL and UNKNOWN page states that comparisons between two null values, or between a null value and any other value, return unknown, and directs you to IS NULL / IS NOT NULL.

When you genuinely want NULL-tolerant equality, where NULL equals NULL and nothing else, every engine has an operator for it, they just disagree on the spelling:

Engine	NULL-safe equality	Status
MySQL 8.4	a <=> b	verified: NULL <=> NULL returns 1, 1 <=> NULL returns 0
PostgreSQL 16	a IS NOT DISTINCT FROM b	verified: returns true for two NULLs, false for 1 vs NULL
SQLite	a IS b	verified; 3.50.6 also accepts IS NOT DISTINCT FROM
SQL Server 2022+	a IS NOT DISTINCT FROM b	per the IS NOT DISTINCT FROM docs; not available before SQL Server 2022

MySQL's comparison operators documentation notes that <=> is equivalent to the standard IS NOT DISTINCT FROM, so all four spellings mean the same thing.

Trap 2: one NULL turns NOT IN into an empty result

Now the trap with real teeth. Find the customers who have no orders:

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

You would expect bob and carol. On MySQL 8.4, PostgreSQL 16, and SQLite 3.50.6 alike, this returns zero rows. Not a wrong list, an empty one, and the same three-valued logic explains it in SQL Server as well.

The subquery produces the list (1, NULL), and id NOT IN (1, NULL) expands to id <> 1 AND id <> NULL. That second comparison is UNKNOWN for every row in the table, and TRUE AND UNKNOWN is UNKNOWN, so no row ever qualifies. One stray NULL in the subquery quietly vetoes the entire result. This is the nastiest variety of NULL bug because the query works perfectly in development and then returns nothing in production the day the first NULL shows up in that column.

Two fixes, both verified to return bob and carol on all three engines. The direct one is to keep NULLs out of the list:

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders
                 WHERE customer_id IS NOT NULL);

The better one is to stop using NOT IN for this job entirely:

SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.id);

NOT EXISTS asks "is there a matching row?" instead of comparing values, so NULLs in the subquery cannot poison it. It states the anti-join intent directly (the same family of shapes covered in SQL joins explained), and it is safe to use as a habit even on columns that are NOT NULL today, because columns have a way of becoming nullable later.

Trap 3: every engine sorts NULLs somewhere different

The first two traps behave identically everywhere. The third is where the engines part ways. Sort three values, one of them NULL:

CREATE TABLE s (v int);
INSERT INTO s VALUES (2), (NULL), (1);
SELECT v FROM s ORDER BY v ASC;

Engine	ASC order	DESC order	NULLS FIRST/LAST syntax
MySQL 8.4	NULL, 1, 2	2, 1, NULL	not supported
PostgreSQL 16	1, 2, NULL	NULL, 2, 1	supported
SQLite 3.50.6	NULL, 1, 2	2, 1, NULL	supported since 3.30 (2019)
SQL Server	NULL first (docs)	NULL last (docs)	not supported

MySQL and SQLite treat NULL as smaller than every value, so it leads an ascending sort. PostgreSQL treats NULL as larger, so it trails. SQL Server sides with MySQL: the ORDER BY clause documentation states that NULL values are treated as the lowest possible values. The practical consequence: port a "latest items first, blanks at the bottom" query from MySQL to Postgres and the blanks migrate from bottom to top with no error and no warning.

Where the standard syntax exists, pinning the position is trivial. Verified on PostgreSQL 16 and SQLite 3.50.6:

SELECT v FROM s ORDER BY v ASC NULLS LAST;   -- 1, 2, NULL

On MySQL 8.4 that syntax is a hard error (ERROR 1064), but a boolean sort key does the same job, verified to return 1, 2, NULL:

SELECT v FROM s ORDER BY (v IS NULL), v;

v IS NULL is 0 for values and 1 for NULLs, so NULLs sink to the end. SQL Server lacks the syntax too; the equivalent trick there is ORDER BY CASE WHEN v IS NULL THEN 1 ELSE 0 END, v, using the conditional ordering pattern shown in the same ORDER BY documentation.

Chasing this class of bug across engines is considerably less painful when you can run the identical script against MySQL, PostgreSQL, SQL Server, and SQLite connections in one place and compare the grids, which is precisely the sort of side-by-side work SQLPro Studio exists for.

The habits that make all three traps a non-issue: write IS NULL rather than = NULL always, reach for NOT EXISTS rather than NOT IN under a subquery, and never let a query's correctness depend on where the engine happens to put NULLs in a sort. Declare it with NULLS LAST or a boolean sort key, and the query means the same thing everywhere.

---