Determine Oracle null == null

Question

I wish to search a database table on a nullable column. Sometimes the value I'm search for is itself NULL. Since Null is equal to nothing, even NULL, saying

where MYCOLUMN=SEARCHVALUE

will fail. Right now I have to resort to

where ((MYCOLUMN=SEARCHVALUE) OR (MYCOLUMN is NULL and SEARCHVALUE is NULL))

Is there a simpler way of saying that?

(I'm using Oracle if that matters)

it's rather obvious that `NVL` will "slow things down" especially for indexed columns. — Anjan Biswas, Aug 10 '12 at 21:06

score 76 · Accepted Answer · answered Oct 10 '08 at 14:42

You can do the IsNull or NVL stuff, but it's just going to make the engine do more work. You'll be calling functions to do column conversions which then have to have the results compared.

Use what you have

where ((MYCOLUMN=SEARCHVALUE) OR (MYCOLUMN is NULL and SEARCHVALUE is NULL))

score 39 · Answer 2 · answered Oct 10 '08 at 16:12

@Andy Lester asserts that the original form of the query is more efficient than using NVL. I decided to test that assertion:

    SQL> DECLARE
      2    CURSOR B IS
      3       SELECT batch_id, equipment_id
      4         FROM batch;
      5    v_t1  NUMBER;
      6    v_t2  NUMBER;
      7    v_c1  NUMBER;
      8    v_c2  NUMBER;
      9    v_b   INTEGER;
     10  BEGIN
     11  -- Form 1 of the where clause
     12    v_t1 := dbms_utility.get_time;
     13    v_c1 := dbms_utility.get_cpu_time;
     14    FOR R IN B LOOP
     15       SELECT COUNT(*)
     16         INTO v_b
     17         FROM batch
     18        WHERE equipment_id = R.equipment_id OR (equipment_id IS NULL AND R.equipment_id IS NULL);
     19    END LOOP;
     20    v_t2 := dbms_utility.get_time;
     21    v_c2 := dbms_utility.get_cpu_time;
     22    dbms_output.put_line('For clause: WHERE equipment_id = R.equipment_id OR (equipment_id IS NULL AND R.equipment_id IS NULL)');
     23    dbms_output.put_line('CPU seconds used: '||(v_c2 - v_c1)/100);
     24    dbms_output.put_line('Elapsed time: '||(v_t2 - v_t1)/100);
     25  
     26  -- Form 2 of the where clause
     27    v_t1 := dbms_utility.get_time;
     28    v_c1 := dbms_utility.get_cpu_time;
     29    FOR R IN B LOOP
     30       SELECT COUNT(*)
     31         INTO v_b
     32         FROM batch
     33        WHERE NVL(equipment_id,'xxxx') = NVL(R.equipment_id,'xxxx');
     34    END LOOP;
     35    v_t2 := dbms_utility.get_time;
     36    v_c2 := dbms_utility.get_cpu_time;
     37    dbms_output.put_line('For clause: WHERE NVL(equipment_id,''xxxx'') = NVL(R.equipment_id,''xxxx'')');
     38    dbms_output.put_line('CPU seconds used: '||(v_c2 - v_c1)/100);
     39    dbms_output.put_line('Elapsed time: '||(v_t2 - v_t1)/100);
     40  END;
     41  /


    For clause: WHERE equipment_id = R.equipment_id OR (equipment_id IS NULL AND R.equipment_id IS NULL)
    CPU seconds used: 84.69
    Elapsed time: 84.8
    For clause: WHERE NVL(equipment_id,'xxxx') = NVL(R.equipment_id,'xxxx')
    CPU seconds used: 124
    Elapsed time: 124.01

    PL/SQL procedure successfully completed

    SQL> select count(*) from batch;

  COUNT(*)
----------
     20903

SQL>

I was kind of surprised to find out just how correct Andy is. It costs nearly 50% more to do the NVL solution. So, even though one piece of code might not look as tidy or elegant as another, it could be significantly more efficient. I ran this procedure multiple times, and the results were nearly the same each time. Kudos to Andy...

Also consider that doing those conversions means that the engine cannot use an index if there's one on the column. For example, doing this: where ? = foo + 1 cannot use an index on foo, but where ? - 1 = foo can use the index. You'll run into this with date indexes a lot. — Andy Lester, Oct 10 '08 at 18:19
Here is Tom's benchmark: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7806711400346248708 — Vadzim, Oct 10 '16 at 18:23

score 14 · Answer 3 · answered Mar 14 '11 at 20:17

14

In Expert Oracle Database Architecture I saw:

WHERE DECODE(MYCOLUMN, SEARCHVALUE, 1) = 1

answered Mar 14 '11 at 20:17

Peter Meinl

2,566
25
39

score 13 · Answer 4 · edited Sep 13 '19 at 15:07

13

I don't know if it's simpler, but I've occasionally used

WHERE ISNULL(MyColumn, -1) = ISNULL(SearchValue, -1)

Replacing "-1" with some value that is valid for the column type but also not likely to be actually found in the data.

NOTE: I use MS SQL, not Oracle, so not sure if "ISNULL" is valid.

edited Sep 13 '19 at 15:07

Marcus Leon

55,199
118
297
429

answered Oct 10 '08 at 14:40

Chris Shaffer

32,199
5
49
61

2

The equivalent Oracle function is NVL. Same syntax. – JosephStyons Oct 10 '08 at 14:42
1

Just as an aside, you could do the same thing with Coalesce(MyColumn, -1) = Coalesce(SearchValue, -1). – Craig Oct 10 '08 at 14:59
5

I'm not entirely comfortable with the proposed criteria for the selection of the NULL replacement value. Is the criteria "not likely to be found" sufficient? What happens when an 'unlikely' value appears on either side of the equality test. Is it now acceptable to match a NULL with a an 'unlikely' value? – spencer7593 May 29 '09 at 03:03
4

This falls apart if either value could legitimately be -1, and then you'll be pulling out your hair to find the bug. – Andy Lester Oct 09 '09 at 19:53

score 8 · Answer 5 · answered Oct 10 '08 at 14:40

8

Use NVL to replace null with some dummy value on both sides, as in:

WHERE NVL(MYCOLUMN,0) = NVL(SEARCHVALUE,0)

answered Oct 10 '08 at 14:40

JosephStyons

57,317
63
160
234

1

what happens if mycolumn is null but searchvalue is 0; or vice versa? – dlamblin Oct 10 '08 at 14:48
3

If 0 is something that can occur in your data or in the search value, then you need to use a different dummy value. If you can't come up with a dummy value that is appropriate, you can't use this approach. – Dave Costa Oct 10 '08 at 14:52
4

You have no need to use this approach at all. There's no reason to compare to dummy values when you can compare directly to actual NULL. – Andy Lester Oct 09 '09 at 19:53

score 7 · Answer 6 · answered Oct 10 '08 at 14:46

7

Another alternative, which is probably optimal from the executed query point of view, and will be useful only if you are doing some kind of query generation is to generate the exact query you need based on the search value.

Pseudocode follows.

if (SEARCHVALUE IS NULL) {
    condition = 'MYCOLUMN IS NULL'
} else {
    condition = 'MYCOLUMN=SEARCHVALUE'
}
runQuery(query,condition)

answered Oct 10 '08 at 14:46

Vinko Vrsalovic

330,807
53
334
373

1

except that you should use a bind variable! – Dave Costa Oct 10 '08 at 14:54
I don't think you could use a bind variable here without defeating your purpose – May 02 '11 at 10:35

score 2 · Answer 7 · edited Nov 19 '15 at 15:30

2

Try

WHERE NVL(mycolumn,'NULL') = NVL(searchvalue,'NULL')

edited Nov 19 '15 at 15:30

bluish

26,356
27
122
180

answered Oct 10 '08 at 14:39

DCookie

42,630
11
83
92

2

what happens if mycolumn is null but searchvalue is 'NULL'; or vice versa? – dlamblin Oct 10 '08 at 14:51
3

The resolution of this comment is left as an exercise to the reader ;-) – DCookie Oct 10 '08 at 15:06
1

I don't get the downvotes. Is the answer incorrect? If so, have the courtesy to state your objections. Perhaps I could adjust my answer to address them. As for the original comment, this was a conceptual illustration. I thought it was pretty clear that one should use a value that won't appear in your data... – DCookie Dec 14 '10 at 16:10

score 2 · Answer 8 · answered Nov 09 '08 at 10:07

2

If an out-of-band value is possible:

where coalesce(mycolumn, 'out-of-band') 
    = coalesce(searchvalue, 'out-of-band')

answered Nov 09 '08 at 10:07

Ted

1,780
1
11
5

EvilTeach · Answer 9 · 2011-05-02T15:26:30.553

1

This can also do the job in Oracle.

WHERE MYCOLUMN || 'X'  = SEARCHVALUE || 'X'

There are some situations where it beats the IS NULL test with the OR.

I was also surprised that DECODE lets you check NULL against NULL.

WITH 
TEST AS
(
    SELECT NULL A FROM DUAL
)
SELECT DECODE (A, NULL, 'NULL IS EQUAL', 'NULL IS NOT EQUAL')
FROM TEST

edited May 02 '11 at 15:26

answered Dec 09 '08 at 02:30

EvilTeach

28,120
21
85
141

2

In standard SQL, concatenation with a NULL yields NULL, so you would end up with NULL = NULL, which isn't true. Does Oracle allow that to work? – Jonathan Leffler Dec 09 '08 at 02:49
1

Ya, I've used it in oracle successfully. – EvilTeach Dec 10 '08 at 02:54
I'd like to change my downvote (sorry about my hastiness) to an upvote which I can't do unless you edit your answer. It might be good to add a note about the datatype of `MYCOLUMN` and `SEARCHVALUE` - does this work for all character data types, all numeric? any other? – May 02 '11 at 10:31
@jack. I haven't done a formal investigation into all datatypes. I have used it with VARCHAR2, so worst case, you can typecast it into a VARCHAR2. – EvilTeach May 02 '11 at 15:31

score 0 · Answer 10 · edited Nov 19 '15 at 15:30

This is a situation we find ourselves in a lot with our Oracle functions that drive reports. We want to allow users to enter a value to restrict results or leave it blank to return all records. This is what I have used and it has worked well for us.

WHERE rte_pending.ltr_rte_id = prte_id
  OR ((rte_pending.ltr_rte_id IS NULL OR rte_pending.ltr_rte_id IS NOT NULL)
      AND prte_id IS NULL)

score 0 · Answer 11 · answered Oct 10 '08 at 14:42

0

I would think that what you have is OK. You could maybe use:

where NVL(MYCOLUMN, '') = NVL(SEARCHVALUE, '')

answered Oct 10 '08 at 14:42

Carl

5,881
4
25
24

2

This won't work because the empty string is equivalent to NULL in Oracle, which brings us back to the same NULL == NULL comparison. – Erick B Oct 20 '08 at 18:24
3

Damnit, I remember that little bit of stupidity now! I think that I'll leave this answer as a reminder to anyone else – Carl Oct 21 '08 at 08:24

Determine Oracle null == null

11 Answers11

Linked