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:
id ---- 1 2 (null) (null)
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.
You can join null values using decode:
on decode(t1.id, t2.id, 1, 0) = 1
decode treats nulls as equal, so this works without "magic" numbers. The two columns must have the same data type.
It won't make the most readable code, but probably still better than
t1.id = t2.id or (t1.id is null and t2.id is null)
Why can't you use null values in joins? In Oracle, both of the following don't evaluate to true:
NULL = NULL
NULL <> NULL
That's why we have
IS NULL /
IS NOT NULL to check for null values.
To test this, you can simply do:
SELECT * FROM table_name WHERE NULL = NULL
Joins are evaluating a boolean condition, and they didn't program them to operate any differently. You can put a greater-than sign in the join condition and add other conditions; it just evaluates it as a boolean expression.
I guess a null can't be equal to a null in joins for the sake of consistency. It would defy the usual behavior of the comparison operator.
A null value in most relational databases is considered UNKNOWN. It is not to be confused with all HEX zeros. if something contains null (unknown,) you cannot compare it.
Unknown = Known False Unknown = Unknown False Unknown >= Known False Known >= Unknown False
Which means, anytime you have a null as an operand in a boolean expression, the else part will always be true.
Contrary to the general hatred towards null by the developers, null has its place. If something is unknown, use null.