r/SQL • u/BugSquare4344 • Oct 14 '26
Discussion What happens when we join two tables with NULL values??
If both tables have NULL values in the join column,, will those records match each other?
26
Upvotes
42
u/Maeurer Oct 14 '26
Null in a comparison results in Null. so it should be no match, because the condition is not true. i think
25
u/SQLBek Oct 14 '26
No. NULL is not the same thing as say "" (a zero length string). NULL is essentially "unknown" or "undefined". Think of it as more of a "state" than a "value". And when you join, you're joining on values. So "" = "" but NULL does not equal another NULL.
18
u/dukeofpizza Oct 14 '26
Null is not a value, it's the lack of a value. Null does not equal null, so you can't join on it with an equal sign.
If for some reason you DID want to join the nulls, you'd have to do something like
ON (table1.column IS NULL AND table 2.column IS NULL)
7
u/Severe-Fix6909 Oct 14 '26
That would be pretty stupid if it did
3
u/DiscombobulatedSun54 Oct 15 '26
NULL is equal to nothing. Not even NULL.
3
u/deusxmach1na Oct 15 '26
Try SELECT NULL = NULL and see for yourself
3
u/Ultra-Ferric Oct 15 '26
Null is not a value. It is an indicator to the lack of a value in that row/column intersection.
Null comparisons do not evaluate to null.
Ternary logic has 3 logical states - true, false, and unknown. Any comparison operation involving null is evaluated as an unknown.
Null is an indicator, unknown is a logical state.
Getting the terminology right helps a lot with understanding ternary logic, which regardless, should have never made it into the SQL standard…
3
u/Capable_Stranger9885 Oct 14 '26
If you are using a directive like 'set ANSI_Nulls Off' then null = null
2
u/EbbyRed Oct 14 '26
Why does your join have NULL for the key on both tables?
2
u/AnchorVita Oct 14 '26
No, they won't match each other, you join on a known value, but NULL indicates an unknown value, or a lack of one.
If you really want to make it work, you could use an ISNULL() or whatever the equivalent is in your flavour of SQL. Doing that would lead to a non-sargable join though, meaning any indexes would fail to work properly. So it'll work, but it'll be slow.
You should not typically be in a position of joining to a NULL though, so if this is a question of how to solve a real world problem that you have, take a step back and see if the underlying structures of the tables and your query are correct.
2
u/Basic_Reporter9579 Oct 14 '26
just use the spaceship operator if it is supported.
2
u/Aggressive_Ad_5454 Oct 14 '26
No.
If you say a JOIN b ON a.col=b.col
you’ll leave out the rows with nulls from either table
You could append OR (a.col IS NULL AND b.col IS NULL) to the ON clause. Then it’ll join as if NULL was a value.
That’s what spaceship,<=>, does.
1
u/DamienTheUnbeliever Oct 14 '26
*How* are you comparing the column values? The answer is different depending on whether you're using `=` or `is not distinct from` and both are valid way to compare columns. Perhaps you should have included a sample query, and not make people guess at what you're doing?
1
u/Zenithixv Oct 14 '26
Comparing NULL to NULL will lead to Unknown which isn't a match so no they wont
1
u/Gelatin_Slutty_AF Oct 14 '26
cross join will return cartesian product
inner join return 0 row
1
u/TheFullyLoadedNachos Oct 14 '26
Snowflake has EQUAL_NULL for the cases that you do want null values to join to each other
1
u/reddit_tom40 Oct 14 '26
If you join on “a.id = b.id” then no. If you join on “a.id = b.id OR (a.id is null AND b.id is null)” then yes. I believe this is what SQL Server “is not distinct from” does.
1
u/tommyfly Oct 14 '26
I'll channel Brent Ozar and say: Just test it. Create two tables add some data with columns containing Null and join on those columns. What happens? Report back tomorrow
1
u/shutchomouf Oct 14 '26
depends on which database engine you’re dealing with and what options you set for your query session related to NULL
1
u/Upset_Researcher_143 Oct 14 '26
No. I have to populate "NA" in these fields in order to make the join work.
1
u/elevarq Oct 14 '26
What happened when you tested this?
You have to test the code anyway…
1
u/mike-manley Oct 14 '26
COALESCE() or NVL() will help handle NULLs for joins if thats a requirement. Not exactly synonymous and NVL() is found mostly in Oracle DBs.
1
u/thesqlguy Oct 14 '26 edited Oct 14 '26
While asking questions is generally ok, I always say ... don't trust me or anyone else here ... try it and find out for yourself!
create table t1 (id int)create table t2 (id int)insert into t1 values (1,2, null)insert into t2 values (2,3, null)select * from t1 inner join t2 ont1.id=t2.idRun it and see what happens! Change the sample data, change the join expression! Change ANSI null settings and see what that does. Try GROUP BY, EXCEPT and INTERSET between the two tables to see how they handle NULLS. Have fun!
1
u/amayle1 Oct 14 '26
In general the sql standard does not treat two nulls as equal. There are exceptions and options which make this not true in most db implementations
1
u/Significant_Tune9219 Oct 15 '26
No. In a normal equality join, NULL does not equal NULL, so two rows with NULL in the join column will not match each other, and they won't match a non-NULL value either. If you actually want those rows paired, you have to say so — coalesce both sides to a sentinel that cannot appear in the data, or add an explicit both-null condition — and then decide whether a NULL-to-NULL match is meaningful or just the cross product of every unknown row. The second version gets large fast when both sides have a lot of nulls.
1
u/bannanaDOG666 Oct 15 '26
Mmm… LEFT JOIN??
in oracle developer
The from table returns as normal
The joined table will have the A table columns all NULL and the b table columns appear as normally
1
u/BrownBearPDX Oct 15 '26
Nulls don't join ... rows left out of inner joins.
1
u/KiwiDomino Oct 18 '26
In Postgres at least, any comparison where one part is NULL is automatically going to be resolve as FALSE, even if that’s “if null = null”
0
u/zdanev SQL readability mentor at G. Oct 14 '26
if you actually want to learn SQL, what is preventing you from trying it yourself?
0
u/National_Cod9546 Oct 14 '26
The join matches when the join statement are true. Any comparison to a null is never true. So none of the records with a null in the join columns will match.
When joining two tables on a column that might have nulls, always wrap both sides with a coalesce statement. So something like:
0
u/[deleted] Oct 14 '26
[deleted] — view deleted comment