eqNullSafe (<=>)
Equality where null matches null: the fix for joins and comparisons that silently drop null keys.
On this page
Show code in
Every code block on the page follows this.
You will learn
- Why null = null is not true in Spark
- How eqNullSafe and <=> compare nulls as equal
- How to join on nullable keys without losing rows
- How to detect changed rows when values can be null
a == b is null when either side is null. a.eqNullSafe(b) (SQL: a <=> b, or a IS NOT DISTINCT FROM b) is true when both are null and false when only one is. Use it for joins and comparisons on nullable columns.The problem
In SQL logic, null means "unknown". Is one unknown equal to another unknown? The answer is unknown, so null = null is null, and filters and joins treat null as false.
| a | b | a = b | a <=> b |
|---|---|---|---|
| 1 | 1 | true | true |
| 1 | 2 | false | false |
| 1 | null | null | false |
| null | null | null | true |
Step by step: a join on a nullable key
Targets and actuals are keyed by country and region, and region is null for country-level rows. A normal join on both columns loses those rows:
left joined to right on country and region
| country | region | target |
|---|---|---|
| IN | North | 100 |
| IN | null | 40 |
| US | null | 70 |
equality join
| country | region | target | actual |
|---|---|---|---|
| IN | North | 100 | 90 |
Two of the three rows disappear because null = null is not true. A null-safe join keeps all three.
Run the example
cond = (left["country"] == right["country"]) & left["region"].eqNullSafe(right["region"]) result = left.join(right, cond).select( left["country"], left["region"], "target", "actual")
SELECT l.country, l.region, l.target, r.actual FROM left l JOIN right r ON l.country = r.country AND l.region <=> r.region
Switch to PySpark to edit and run this example in your browser.
Detecting changed rows
When comparing yesterday's and today's version of a record, old.email != new.email is null when either side is null, so a change from null to a value is missed. The null-safe "not equal" is ~old.email.eqNullSafe(new.email), or in SQL NOT (old.email <=> new.email) / old.email IS DISTINCT FROM new.email.
Under the hood
Spark can still use a hash or sort-merge join with a null-safe key: it rewrites a <=> b into an equi-join on coalesce(a, default) plus an isnull(a) flag. That has a cost: every null key now lands in the same partition. If most keys are null, one task gets most of the data. Check the null share of a column before joining null-safely on it.
"ALL" at ingestion, so the key means something and plain equality works.Common mistakes
Expecting null keys to match in a join
Detecting changes with !=
Null-safe joins on mostly-null keys
Key takeaways
- null = null is null, which filters and joins treat as false.
- eqNullSafe / <=> treats two nulls as equal.
- Use the null-safe comparison to detect changes involving nulls.
- Null-safe joins send all null keys to one partition.
Check yourself
3 questions1. What is NULL <=> NULL?
Show the answer
true. The null-safe operator treats two nulls as equal.
2. What is 1 <=> NULL?
Show the answer
false. It never returns null: one null and one value are not equal.
3. Which detects a change from null to "a@x.com"?
Show the answer
NOT (old <=> new). old != new is null in that case. The null-safe form is false for equality, so NOT gives true.
Practice it
Interview problems that use eqNullSafe (<=>): write the PySpark, run it, and get graded on hidden tests.
Go deeper
Primary sources: Column.eqNullSafe · NULL semantics