Simplest SQL expression to check if two columns have same value accounting for NULL

Question

I am trying to figure out the simplest generalized SQL expression that can check if two columns a and b are the same. In other words, an expression that evaluates to true when:

a is NULL and b is NULL; or
a is not NULL and b is not NULL and a = b

Assume columns a and b have exactly the same data type.

The most obvious solution, which I'm using in the below example, is horribly convoluted, particularly because I need to repeat this clause 15x in a 15-column table:

SELECT * FROM (

    SELECT 'x' a, 'x' b FROM dual
        UNION ALL
    SELECT 'x' a, NULL b FROM dual
        UNION ALL
    SELECT NULL a, 'x' b FROM dual
        UNION ALL
    SELECT NULL a, NULL b FROM dual
        UNION ALL
    SELECT 'x' a, 'y' b FROM dual
        UNION ALL
    SELECT 'x' a, NULL b FROM dual
        UNION ALL
    SELECT NULL a, 'y' b FROM dual
        UNION ALL
    SELECT NULL a, NULL b FROM dual

)

WHERE (a IS NULL AND b IS NULL) OR
      (a IS NOT NULL AND b IS NOT NULL AND a = b)
/

And the expected result is:

+--------+--------+
|   a    |   b    |
+--------+--------+
| x      | x      |
| (null) | (null) |
| (null) | (null) |
+--------+--------+

tl;dr - Can I simplify my WHERE clause, ie make it more compact, while keeping it logically correct?

P.S.: I couldn't give a damn about any SQL purist insistence that "NULL is not a value". For my practical purposes, if a contains NULL and b does not, then a differs from b. It is not "unknown" whether they differ. So please, in advance, no arguments up that alley!

P.P.S.: My SQL flavour is Oracle 11g.

P.P.P.S.: Someone decided this question is a duplicate of "Is there better Oracle operator to do null-safe equality check?" but a cursory check in that question will show that the answers are less helpful than the ones posted on this thread and do not satisfy my particular, and explicitly-stated criteria. Just because they are similar doesn't make them duplicates. I've never understood why people on SO work so hard to force my problem X to be someone else's problem Y.

I usually use `ISNULL()` function in SQL Server, I think `NVL()` is equivalent in Oracle, maybe try to take a look at that function? — xbb, Dec 10 '15 at 20:28
`LNNVL` in that duplicate question is exactly what you are looking for. — Noel, Dec 11 '15 at 04:56
You're right. I'm blind. :(. Thanks @Noel. I didn't realize `LNNVL` is superior to `DECODE` but it is. I just voted close on my own question :) — 0xbe5077ed, Dec 11 '15 at 19:04

score 2 · Answer 1 · answered Dec 10 '15 at 20:26

2

You can readily simplify it as:

WHERE (a IS NULL AND b IS NULL) OR
      (a = b)

The IS NOT NULL is not needed.

If you have a "safe" value (i.e. one that is never used), you can do this:

WHERE COALESCE(a, ' ') = COALESCE(b, ' ')

This assumes that ' ' is not a valid value.

answered Dec 10 '15 at 20:26

Gordon Linoff

1,242,037
58
646
786

The first one is definitely better. I did not realize that [the truth table for TRUE OR UNKNOWN](https://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions004.htm#sthref2753) produced TRUE. I don't, however, have a safe value to use with `COALESCE`. – 0xbe5077ed Dec 10 '15 at 20:33

score 1 · Answer 2 · answered Dec 10 '15 at 20:37

1

I have found the Ask Tom article "Safely Comparing NULL Columns As Equal" to be the most helpful. In Oracle, you can use the DECODE function to do this:

 WHERE 1 = DECODE(a, b, 1, 0)

And this is the most compact solution I have seen so far.

answered Dec 10 '15 at 20:37

0xbe5077ed

4,565
6
35
77

score 0 · Answer 3 · answered Dec 10 '15 at 20:24

0

Simple is not necessarily performant.

consider this possibility.

WHERE X || 'x' = Y || 'x'

If you want to really push the envelope, use the SYS_OP_MAP_NONNULL

answered Dec 10 '15 at 20:24

EvilTeach

28,120
21
85
141

I am not concerned with performant. I'm concerned only with compactness of the expression. – 0xbe5077ed Dec 10 '15 at 20:28
Interesting. What is the business condition that is making that a concern? – EvilTeach Dec 10 '15 at 20:29
Compactness and readability of code? A general bias toward being simple and terse instead of verbose and impossible to read. And I think you can safely assume since I stated compactness is the goal that I know enough about the data I am dealing with to know performance of the `WHERE` clause just isn't a big deal. – 0xbe5077ed Dec 10 '15 at 20:41

score -1 · Answer 4 · answered Dec 13 '15 at 08:11

-1

SELECT * FROM (

SELECT 'x' a, 'x' b FROM dual
    UNION ALL
SELECT 'x' a, NULL b FROM dual
    UNION ALL
SELECT NULL a, 'x' b FROM dual
    UNION ALL
SELECT NULL a, NULL b FROM dual
    UNION ALL
SELECT 'x' a, 'y' b FROM dual
    UNION ALL
SELECT 'x' a, NULL b FROM dual
    UNION ALL
SELECT NULL a, 'y' b FROM dual
    UNION ALL
SELECT NULL a, NULL b FROM dual

)

WHERE NVL(a,'1')=NVL(b,'1')

answered Dec 13 '15 at 08:11

Sergiu Velescu

500
3
9

Although '1' isn't in my example dataset, you can readily imagine that it might be in a real-world dataset. I would like a solution that doesn't require assuming that a given value won't appear in the dataset. See my comment to @Gordon Linoff's answer below: "I don't, however, have a safe value to use with COALESCE". – 0xbe5077ed Dec 15 '15 at 01:16
You could replace '1' with something that will never appear in your dataset (it could be 'xxxxx' or 'aaaaa' or anything else) – Sergiu Velescu Dec 16 '15 at 11:11

Simplest SQL expression to check if two columns have same value accounting for NULL

4 Answers4