Why can't I use null values in joins?

I have solved the query problem by using ...row_number() over (partition by... this is a more general question on why we cannot use columns with null values in joins. Why can't a null be equal to a null for the sake of a join?

Why can't a null be equal to a null for the sake of a join?

Just tell Oracle to do that:

select *
from one t1 
  join two t2 on coalesce(t1.id, -1) = coalesce(t2.id, -1);

(Note that in standard SQL you could use t1.id is not distinct from t2.id to get a null-safe equality operator, but Oracle does not support that)

But this will only work if the replacement value (-1 in the example above) does not actually appear in the table. Finding such a "magic" value for numbers might be possible, but it will be very difficult for character values (especially because Oracle treats an empty string as null as well)

Plus: no index on the id columns will be used (you could define a function based index with the coalesce() expression though).

Another option that works for all types, without magic values:

on t1.id = t2.id or (t1.id is null and t2.id is null)

But the real question is: does this make sense?

Consider the following sample data:

Table one

id
----
1
2
(null)
(null)


Table two

id
----
1
2
(null)
(null)
(null)


Which of combination of null values should be chosen in the join? My above example will result in something like a cross join for all null values.

T1_ID | T2_ID
-------+-------
1 | 1
2 | 2
(null) | (null)
(null) | (null)
(null) | (null)
(null) | (null)
(null) | (null)
(null) | (null)


StackExchange Database Administrators Q#165798, answer score: 31