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.