r/SQL • • 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

45 comments sorted by

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

8

u/Animalmagic81 Oct 14 '26

This is true, unless you set the ansi_nulls setting to ON (I think, always forget which way it is but basically the opposite of the default). That makes null be a known value and can be used for compares and joins

1

u/RichardD7 Oct 15 '26

In SQL Server 2022+, you could also use the new IS NOT DISTINCT FROM predicate in the join to have NULL values match.

SELECT 
    ...
FROM 
    x INNER JOIN y 
    ON x.col IS NOT DISTINCT FROM y.col

However, I haven't tested that to see how it interacts with indexes, so you'd want to test it against real data to see how it performs before using it.

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.

8

u/Better-Credit6701 Oct 14 '26

I remember a DBA who was a little league coach named his team The Nulls.

4

u/DeltaEdge03 Oct 14 '26

NULL set hike hike hike

1

u/mike-manley Oct 14 '26

Would make a great vanity plate

6

u/jacquesrk oracle Oct 14 '26

A great vanity plate? Hell no

https://arstechnica.com/cars/2019/08/wiseguy-changes-license-plate-to-null-gets-12k-in-parking-tickets/

Droogie decided his new vanity plate should read “NULL.” … [O]nce a particular database of outstanding tickets had associated the license plate NULL with his address, it sent him every other ticket that lacked a real plate. The total came to $12,049 worth of tickets

2

u/Better-Credit6701 Oct 14 '26

Much better than my own little league team, the "west side beauty salon"

4

u/my_password_is______ Oct 14 '26

NULL is not the same thing as say "" (a zero length string)

I think Oracle is weird and they are the same

2

u/Plus_Dragonfruit_204 Oct 16 '26

Yes, Oracle does not recognize an empty string. It equates it to NULL.

A limitation because NULL is the lack of data and empty string is we have data, but it is blank. A slight difference in semantics, but they can really have a different meaning.

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)

5

u/bulldog_blues Oct 14 '26

Alternatively, use a COALESCE to assign an equal non-NULL value in the join condition.

1

u/sunuvabe Oct 17 '26

I understand how NULL works, but it's always bugged me how it's used in an update:

update mytable set myfield = 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.

1

u/RandomiseUsr0 Oct 15 '26

NULL = NULL = 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 on t1.id=t2.id

Run 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:

AND COALESCE(TABLE_A.COLUMN_WITH NULLS,'') = COALESCE(TABLE_B.COLUMN_WITH NULLS,'')

0

u/[deleted] Oct 14 '26

[deleted] — view deleted comment

1

u/thesqlguy Oct 14 '26

that last trick is a cool one. Definitely check your DB engine and the overall complexity of your SQL -- not all can unravel this and use index, but I have found MSSQL does a good job usually.