3 Replies Latest reply on Nov 1, 2013 8:23 PM by JohnWatson

    query testing for null value in IN (null,'a','b','c')






      I belive the following query is incorrect in my opinion. (there is an index on col but NULLS are ignored?


      SELECT COUNT(*) from <TABLE> where col in (null,'a','b','c')


      this works (no errors) and returns pretty quick.


      However I think the correct query would be


      SELECT COUNT(*) from <TABLE> where col IS NULL OR col in ('a','b','c')


      This one takes a long time. As I see it it does a table scan for NULL part and uses the index for the rest as the index cannot be used for NULL values.


      I would appreicate an explanation on this, especially why Oracle accepts the first query "where col in (null,'a','b','c')" without any issue.



        • 1. Re: query testing for null value in IN (null,'a','b','c')

          A null is not equal to anything, not even another null. This is according to the ANSI definition. So your first query is not doing what you think it is doing, though it is not syntactically wrong.

          The second query is correct. If you want to use an index, you have to create a bitmap index (which requires Enterprise Edition licences) as these do include nulls, unlike B*tree indexes.

          • 3. Re: query testing for null value in IN (null,'a','b','c')

            It is instructive to see how Oracle re-writes the query, to ignore the null condition:


            orclz> set autot on exp


            orclz> select * from emp where ename in (null,'a','b');


            no rows selected



            Execution Plan


            Plan hash value: 3956160932



            | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |


            |   0 | SELECT STATEMENT  |      |     1 |    39 |     3   (0)| 00:00:01 |

            |*  1 |  TABLE ACCESS FULL| EMP  |     1 |    39 |     3   (0)| 00:00:01 |



            Predicate Information (identified by operation id):



               1 - filter("ENAME"='a' OR "ENAME"='b')