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

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

    905989

      Hi,

       

      Oracle 10.2.0.4

       

      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.

       

      Thanks

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

          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.

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

            many thanks John!

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

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

              orclz>

              orclz> set autot on exp

              orclz>

              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')

               

              orclz>