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?

31

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)
| improve this answer | |
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.

| improve this answer | |
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.

| improve this answer | |
  • 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
2

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)

| improve this answer | |
1

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.

| improve this answer | |
  • NULL = anything results in NULL because the SQL standard says so. A row satisfies the join condition only if the expression is true. – Laurenz Albe Sep 23 '19 at 16:51
  • 1
    Beyond the literal implementation detail (which isn't always the case: some DBs have the option to equate NULL to NULL for some/all purposes) there is a logical reason: NULL is unknown. When you compare NULL to NULL you are asking "is this unknown thing equal to this other unknown thing" to which the only reasonable answer is "unknown" - another NULL (which is mapped to false in a comparison situation). – David Spillett Sep 23 '19 at 16:52
-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.

| improve this answer | |
  • 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.