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)