Why can't I use null values in joins?

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)

Alternatively you can make two nulls match each other using INTERSECT as an equality operator:

SELECT
  *
FROM
  t1
  INNER JOIN t2
    ON EXISTS (SELECT t1.ID FROM DUAL INTERSECT SELECT t2.ID FROM DUAL)
;

See this DBFiddle demo for an illustration.

Of course, this looks quite a mouthful, although it is actually not much longer than BriteSponge's suggestion. However, it is certainly not a match, if you pardon the pun, to the conciseness of the mentioned earlier in comments standard way, which is the IS NOT DISTINCT FROM operator, not yet supported in Oracle.


Just for completeness I will mention that the function SYS_OP_MAP_NONNULL can now safely be used to compare values that are null as it is now documented in the 12c documentation. This means Oracle won't just randomly remove it and break your code.

SELECT *
FROM   one t1 
       JOIN two t2
         ON SYS_OP_MAP_NONNULL(t1.id) = SYS_OP_MAP_NONNULL(t2.id)

The advantage being that you don't come across the 'magic' number problem.

The reference in the Oracle docs is at Basic Materialized Views – Choosing Indexes for Materialized Views.

Tags:

Oracle

Null

Join