13

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?

30

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)
6

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.

2

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.

  • So it is documented now? Because AskTom (in 2003) stated: "- it's undocumented, and therefore poses a risk of going away or changing functionality that is enough said that should make people just "stop reading" go there and you might be really mad in the next release. the only CORRECT way is: where (a = b or (a is null and b is null)) period. that is my thoughts on it. I would not consider using sys_op_map_nonnull, ignore that man behind the curtain." – ypercubeᵀᴹ Mar 1 '17 at 10:27
  • If you have link, please add it to the question. I haven't found mention in 12c Functions but searching for Oracle documentation and specific version is rather hard. – ypercubeᵀᴹ Mar 1 '17 at 10:33
-4

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.

  • 6
    Actually all the example comparisons you have, yield UNKNOWN, not FALSE ;) – ypercubeᵀᴹ Mar 1 '17 at 17:17
  • You are right, however the purpose of a boolean expression is to result true or false only, so, let's not get crazy here :). – jujiro Mar 1 '17 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.