NULL Handling in MySQL vs PostgreSQL: Sorting, Unique Constraints, and NOT IN Traps

The sessions table had 14 million rows that should have been deleted and were not. The cleanup job ran nightly on the MySQL 8.0 system: delete sessions whose user no longer exists, written as WHERE user_id NOT IN (SELECT id FROM users). It ran green every night for six months. When we finally investigated the table’s growth, the answer was one row: an ETL change in March had started writing NULL user_ids for anonymous traffic, and the users table had also gained a single NULL id from a bad backfill. One NULL in the subquery result turns NOT IN into UNKNOWN for every candidate row, and UNKNOWN is not true, so the DELETE matched zero rows forever, silently, on schedule.

Then the PostgreSQL migration made the same class of bug visible from the other direction. A dedupe job picked the record to keep with ORDER BY ended_at ASC — NULLs meaning “still active” — and after the port to PostgreSQL 15 it started keeping closed records and discarding active ones. On MySQL, NULL sorts first in ascending order; on PostgreSQL, NULL sorts last. Same query, same data, opposite winner. NULL is the only value in SQL whose semantics are equal parts standard, engine-specific, and invisible, and this article is the field guide to the three places the two engines disagree or agree in ways that hurt: three-valued logic, sort order, and unique constraints.

Why does NOT IN with a NULL match nothing on both engines?

Because three-valued logic makes x NOT IN (a, b, NULL) evaluate to UNKNOWN for every row, and a WHERE clause only passes true. The comparison chain expands to x != a AND x != b AND x != NULL, and any comparison against NULL yields UNKNOWN, which poisons the AND. This is SQL standard behavior and both engines implement it faithfully — which is precisely why the bug ports cleanly and surprises people twice. The fix is the same on both engines, and it is NOT EXISTS, which treats NULLs in the subquery as irrelevant because it tests existence rather than comparing values:

-- broken on both engines the moment users.id contains one NULL:
DELETE FROM sessions WHERE user_id NOT IN (SELECT id FROM users);

-- correct on both engines:
DELETE FROM sessions s
WHERE NOT EXISTS (SELECT 1 FROM users u WHERE u.id = s.user_id);

There is a performance footnote: PostgreSQL’s planner can anti-join a NOT IN when the columns are provably NOT NULL, but with nullable columns it takes a more expensive hashed plan, while NOT EXISTS gets the anti-join regardless — so the rewrite fixes correctness and plan quality at once. If you cannot rewrite, filtering the subquery with WHERE id IS NOT NULL restores correctness but leaves the plan question open. My rule after six silent months: NOT IN with a subquery is guilty until the inner column is declared NOT NULL, and a nightly job that deletes nothing should page someone.

Why do NULLs sort to opposite ends after a migration?

Because MySQL treats NULL as smaller than every value while PostgreSQL treats NULL as larger, so an ascending sort puts NULLs first on MySQL and last on PostgreSQL, and any “pick the top row” logic built on that ordering inverts. PostgreSQL gives you explicit control with NULLS FIRST and NULLS LAST; MySQL has no such clause, and the portable workaround is a boolean sort key:

-- portable: active (NULL ended_at) records first, then by date
-- works identically on MySQL 8 and PostgreSQL
SELECT id, ended_at
FROM subscriptions
ORDER BY ended_at IS NOT NULL, ended_at ASC
LIMIT 1;

-- PostgreSQL-only shorthand for the same ordering:
-- ORDER BY ended_at ASC NULLS FIRST

The dedupe incident’s fix was exactly that IS NOT NULL key, because it states the intent — NULLs first — instead of inheriting an engine default. Watch for the same trap in DESC ordering, where the defaults flip again, and in window functions, where ORDER BY inside OVER follows the same engine-specific NULL placement. Any migration test suite should include one ORDER BY with NULLs in the sort key, because this class of bug never errors; it just quietly keeps the wrong record.

How do unique constraints treat NULL differently now?

Historically both engines allow any number of NULLs in a UNIQUE column — NULLs are not equal to each other, so they cannot conflict — and PostgreSQL 15 added the option to change that with NULLS NOT DISTINCT, while MySQL still has no equivalent. The practical consequence for dedupe and upsert logic: on both engines, a UNIQUE constraint on (email) permits unlimited rows with NULL email, so a merge job that assumes “one constraint violation means a real duplicate” must handle the NULL population separately. On PostgreSQL 15 and later you can close the loophole declaratively:

-- PostgreSQL 15+: treat NULLs as conflicting, so only one NULL row exists
CREATE UNIQUE INDEX users_email_unique_nulls_not_distinct
ON users (email) NULLS NOT DISTINCT;

-- checking equality NULL-safely, index-friendly on both engines:
SELECT * FROM users WHERE email IS NOT DISTINCT FROM '[email protected]';  -- PostgreSQL
SELECT * FROM users WHERE email <=> '[email protected]';                    -- MySQL

The NULL-safe equality operators at the bottom deserve their own line in the migration notes: MySQL’s <=> returns true for NULL <=> NULL and can use an index, while PostgreSQL’s IS NOT DISTINCT FROM is the standard spelling with the same semantics. Code that emulates NULL-safe equality with (a = b OR (a IS NULL AND b IS NULL)) ports to neither idiom cleanly and defeats indexes on both. And if your upsert logic leans on unique-violation detection, remember that NULL-heavy columns interact with conflict targets differently per engine — the ON DUPLICATE KEY vs ON CONFLICT comparison covers that surface, and the broader strictness philosophy behind why the engines diverge on data semantics is in the SQL mode vs PostgreSQL strictness notes.

Where do aggregates and joins fit in the NULL audit?

Two shared behaviors round out the audit because they generate the most “the migration changed my numbers” tickets while being identical on both engines. COUNT(column) skips NULLs and COUNT(*) counts rows — same everywhere — but ORMs emit one or the other depending on the query shape, so a ported report can change totals when the ORM’s SQL generation changes, with NULLs as the hidden variable. And joins on nullable keys drop NULL pairs: an INNER JOIN ON a = b never matches NULL to NULL on either engine, so outer-join reconciliation queries after a migration need the NULL-safe comparison operators above or the counts will never tie out. None of this is engine divergence; all of it is NULL doing exactly what the standard says, at the exact moment you are comparing two systems row by row.

Where MonPG fits in a NULL incident

NULL bugs are data bugs, and data bugs surface as growth and drift: a table that should shrink and does not, a nightly job whose affected-row count quietly drops to zero, a dedupe ratio that shifts after a migration. MonPG’s PostgreSQL monitoring keeps the table-size and statement-level history that makes “this DELETE has matched zero rows since March” a dashboard observation instead of a forensic discovery six months later.

MonPG monitors PostgreSQL today; MySQL support is on the roadmap. When it lands, the MySQL signals from this article — job statements with collapsed row counts, tables growing past their retention intent — are the ones it will surface. Until then, the MySQL monitoring page tracks that work. The lesson that needs no monitoring: write NULL handling explicitly — IS NOT NULL sort keys, NOT EXISTS, NULL-safe equality — because the engine defaults are a coin flip, and NULL always wins a coin flip you did not know you were making.

Related documentation

• MySQL: working with NULL
• PostgreSQL: comparison functions