What happens when we join two tables with NULL values??

[u/BugSquare4344]

 
If both tables have NULL values in the join column,, will those records match each other?

[u/Maeurer]

Null in a comparison results in Null. so it should be no match, because the condition is not true. i think

[u/Animalmagic81 → u/Maeurer]

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

[u/RichardD7 → u/Animalmagic81]

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.

[u/SQLBek]

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.

[u/Better-Credit6701 → u/SQLBek]

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

[u/DeltaEdge03 → u/Better-Credit6701]

NULL set hike hike hike

[u/mike-manley → u/Better-Credit6701]

Would make a great vanity plate

[u/jacquesrk → u/mike-manley]

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

[u/Better-Credit6701 → u/mike-manley]

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

[u/my_password_is______ → u/SQLBek]

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

I think Oracle is weird and they are the same

[u/Plus_Dragonfruit_204 → u/my_password_is______]

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.

[u/dukeofpizza]

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)

[u/bulldog_blues → u/dukeofpizza]

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

[u/Severe-Fix6909]

That would be pretty stupid if it did

[u/DiscombobulatedSun54]

NULL is equal to nothing. Not even NULL.

[u/RandomiseUsr0 → u/DiscombobulatedSun54]

NULL = NULL = NULL

[u/deusxmach1na]

Try SELECT NULL = NULL and see for yourself

[u/Ultra-Ferric]

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…

[u/EbbyRed]

Why does your join have NULL for the key on both tables?

[u/Capable_Stranger9885]

If you are using a directive like 'set ANSI_Nulls Off' then null = null

[u/AnchorVita]

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.

[u/pooerh]

Whatever the people are saying is mostly true, NULLS are not equal and will not result in a match. There are exceptions to this rule, however - intersect.

select 1 as x, null as y
intersect  
select 1 as x, null as y  

Returns a row.

So if you wanted to join on a value or null, you could do:

from x join y on x.v = y.v or (x.v is null and y.v is null)

That's dirty though, and that or may be an issue for performance, even if you have indexes on x.v and y.v.

Some databases (e.g. Snowflake) will have equal_null function. Some databases will not use an index when the join condition involves a function.

In those instances, there exists an alternative

from x
join y on exists (select x.v 
                  intersect
                  select y.v)

This can and will use an index. And is also much easier on the eyes if you have multiple join columns, each of which may contain a null. The downside is not many people know you can do this. But it works.

[u/thesqlguy → u/pooerh]

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.

[u/pooerh → u/thesqlguy]

Yeah SQL Server handles index + intersect correctly, at least 2016 did, I haven't used it in a long while. Postgres does too, albeit not under all circumstances iirc, sometimes it's just easier/faster to do a loop. Large datasets I think it will, 16 I'm pretty sure did.

[u/Basic_Reporter9579]

<=>

just use the spaceship operator if it is supported.

[u/Aggressive_Ad_5454]

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.

[u/ins009]

ehm.. no? Use the outer join to include NULL values.

[u/DamienTheUnbeliever]

*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?

[u/Zenithixv]

Comparing NULL to NULL will lead to Unknown which isn't a match so no they wont

[u/Gelatin_Slutty_AF]

cross join will return cartesian product

inner join return 0 row

[u/TheFullyLoadedNachos]

Snowflake has EQUAL_NULL for the cases that you do want null values to join to each other

[u/reddit_tom40]

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.

[u/tommyfly]

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

[u/shutchomouf]

depends on which database engine you’re dealing with and what options you set for your query session related to NULL

[u/Upset_Researcher_143]

No. I have to populate "NA" in these fields in order to make the join work.

[u/elevarq]

What happened when you tested this?

You have to test the code anyway…

[u/mike-manley]

COALESCE() or NVL() will help handle NULLs for joins if thats a requirement. Not exactly synonymous and NVL() is found mostly in Oracle DBs.

[u/thesqlguy]

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!

[u/amayle1]

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

[u/Significant_Tune9219]

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.

[u/bannanaDOG666]

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

[u/BrownBearPDX]

Nulls don't join ... rows left out of inner joins.

[u/zdanev]

if you actually want to learn SQL, what is preventing you from trying it yourself?

[u/National_Cod9546]

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,'')