Get null == null in SQL

Question

I want to search a database table in a column with a null value. Sometimes the value I'm looking for is itself NULL. Since Null equals 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 an easier way to say this?

(I use Oracle if that matters)

Answer 1

You can do IsNull or NVL things, but it just makes the engine do more work. You will call functions to transform the columns, which then must compare the results.

Use what you have

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

Answer 2

@ Andy Lester claims the original request form is more efficient than using the NVL. I decided to check this statement:

  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 surprised to find out how true Andy was. This is almost 50% more than the NVL solution. Thus, while one piece of code may not look as neat or elegant as another, it can be significantly more efficient. I performed this procedure several times, and the results were almost the same every time. Kudos to Andy ...

Answer 3

In the expert architecture of Oracle Database I have seen:

 WHERE DECODE(MYCOLUMN, SEARCHVALUE, 1) = 1 

Answer 4

I don’t know if this is easier, but I sometimes used

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

Replacing "-1" with some value that is valid for the column type, but is also unlikely to be really found in the data.

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

Answer 5

Use NVL to replace null with some dummy value on both sides, for example:

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

Answer 6

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

The following is pseudo code.

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

Answer 7

If out-of-band value is possible:

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

Answer 8

Try

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

Answer 9

It can also do work in Oracle.

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

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

I was also surprised that DECODE allows you to check NULL for NULL.

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

Answer 10

This is the situation we are in a lot of with our Oracle functions that manage reports. We want to allow users to enter a value to limit the results, or leave it blank to return all records. This is what I used and it 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) 

Answer 11

I think everything is fine with you. You could use:

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