List:General Discussion« Previous MessageNext Message »
From:D. Dante Lorenso Date:December 28 2009 7:32pm
Subject:Is anything ever equal to NULL?
View as plain text  
Will anything ever be equal to NULL in a SELECT query?

   SELECT *
   FROM sometable
   WHERE somecolumn = NULL;

I have a real-life query like this:

   SELECT *
   FROM sometable
   WHERE somecolumn = NULL OR somecolumn = 'abc';

The 'sometable' contains about 40 million records and in this query, it 
appears that the where clause is doing a sequential scan of the table to 
  find a condition where 'somecolumn' = NULL.  Shouldn't the query 
parser be smart enough to rewrite the above query like this:

   SELECT *
   FROM sometable
   WHERE FALSE OR somecolumn = 'abc';

And therefor use the index I have on 'somecolumn'?  When I manually 
rewrite the query, I get the performance I expect but when I leave it as 
it was, it's 100 times slower.

What's so special about NULL?

-- Dante

----------
D. Dante Lorenso
Thread
Is anything ever equal to NULL?D. Dante Lorenso28 Dec
  • Re: Is anything ever equal to NULL?Michael Dykman28 Dec
  • Re: Is anything ever equal to NULL?David Giragosian28 Dec
    • Re: Is anything ever equal to NULL?Carsten Pedersen28 Dec
      • Re: Is anything ever equal to NULL?David Giragosian29 Dec
  • Re: Is anything ever equal to NULL?Martijn Tonies28 Dec
    • Re: Is anything ever equal to NULL?D. Dante Lorenso29 Dec
    • Re: Is anything ever equal to NULL?Joerg Bruehe29 Dec
  • Re: Is anything ever equal to NULL?Martijn Tonies29 Dec