If you've worked with MySQL long enough, you've probably been bitten by NULL comparisons at least once. A query that should return results returns nothing. A WHERE clause that should exclude a row doesn't. The culprit is almost always the three-valued logic of SQL — and the null-safe equality operator <=> is one of the cleanest tools for dealing with it.

The problem with = and NULL

In SQL, NULL represents the absence of a value — the unknown. Because of this, any comparison involving NULL using the standard = operator yields NULL (not TRUE or FALSE), and NULL is falsy in a WHERE clause.

SELECT NULL = NULL;   -- NULL
SELECT NULL = 1;      -- NULL
SELECT 1 = 1;         -- 1 (TRUE)

This means:

SELECT * FROM users WHERE deleted_at = NULL;  -- returns nothing

The idiomatic fix is IS NULL / IS NOT NULL, but that only works for literal null checks. The moment you're comparing two columns — one or both of which might be NULL — things get awkward fast.

-- This silently drops rows where either column is NULL
SELECT * FROM orders WHERE shipping_address = billing_address;

Enter <=>

MySQL's null-safe equality operator <=> behaves exactly like =, except it treats NULL as a comparable value. Two NULLs are considered equal, and a NULL compared to any non-null value is FALSE.

SELECT NULL <=> NULL;   -- 1 (TRUE)
SELECT NULL <=> 1;      -- 0 (FALSE)
SELECT 1 <=> 1;         -- 1 (TRUE)
SELECT 1 <=> 2;         -- 0 (FALSE)

This makes it safe to compare nullable columns directly:

-- Correctly includes rows where both columns are NULL
SELECT * FROM orders WHERE shipping_address <=> billing_address;

A real-world example

Consider a polymorphic subscriptions join table that maps users to subscribable entities (posts, documents, threads, etc.). The goal: return all subscribers excluding the item's author, even when author_id might be NULL.

The naive approach breaks silently:

WHERE subscriptions.user_id != posts.author_id

When author_id is NULL, this evaluates to NULL, so the row is dropped — meaning a user who subscribes to a post with no author would incorrectly disappear from the result.

The null-safe fix:

NOT (subscriptions.user_id <=> (
    SELECT author_id FROM posts WHERE id = subscriptions.item_id
))

This reads as: "keep this row unless the subscriber's user_id exactly matches the author_id, treating NULL as a concrete equal value." When author_id is NULL and user_id is not, the <=> returns FALSE, so NOT FALSE is TRUE — the row is kept. Correct behaviour in all cases.

<=> vs. the alternatives

Approach	Handles NULL?	Readable?	Standard SQL?
col = val	No	Yes	Yes
col IS NULL	Yes (literal only)	Yes	Yes
COALESCE(col, '') = COALESCE(val, '')	Yes (with sentinel)	Passable	Yes
(col = val OR (col IS NULL AND val IS NULL))	Yes	Verbose	Yes
col <=> val	Yes	Yes	No (MySQL/MariaDB)

The COALESCE sentinel approach is fragile — you need to pick a value that can never appear in real data. The verbose OR (IS NULL AND IS NULL) pattern works but is noisy. <=> wins on brevity and correctness, at the cost of portability.

When to use it

<=> is a good fit when:

• Comparing two nullable columns directly in a WHERE or JOIN condition.
• Negating equality on nullable data (NOT (a <=> b) is cleaner than the alternative).
• Upsert / deduplication queries where you need exact matching including null identity.
• Generated columns or audit logic where you want to detect whether a value actually changed, including transitions to/from NULL.

Caveats

MySQL and MariaDB only. The <=> operator is not part of the SQL standard and is not available in PostgreSQL, SQLite, or SQL Server. If your codebase runs tests against SQLite (a common Laravel setup), any <=> in a raw query will fail there.

PostgreSQL's equivalent is IS NOT DISTINCT FROM / IS DISTINCT FROM:

-- PostgreSQL equivalent of <=>
col IS NOT DISTINCT FROM val

-- PostgreSQL equivalent of NOT (col <=> val)
col IS DISTINCT FROM val

If cross-database portability matters, abstract the comparison behind a query scope or use the verbose but portable OR (IS NULL AND IS NULL) form.

Summary

The null-safe equality operator <=> is one of those small MySQL features that, once you know it exists, saves you from a whole class of subtle bugs. It's most valuable when you need to compare nullable columns directly — particularly in negated conditions where the standard != would silently swallow NULL rows. Just keep portability in mind: it's a MySQL/MariaDB extension, so make sure your test database matches your production database before reaching for it.