From: Martijn Tonies
Date: December 28 2009 8:16pm
Subject: Re: Is anything ever equal to NULL?
List-Archive: http://lists.mysql.com/mysql/219958
Message-Id: <002a01ca87fa$964fdaf0$1401a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	format=flowed;
	charset="iso-8859-1";
	reply-type=response
Content-Transfer-Encoding: 7bit

Hi,
 
> Will anything ever be equal to NULL in a SELECT query?

No, never.

Null also means "unknown", if you design your tables well enough,
there should be no NULLs -stored- (different from a resultset,
where there can be nulls, for example in LEFT JOINs), because 
it's no use to store what you don't know. The only case when 
you want to store a null is when you do want to -know- you don't 
know a value.

A column can have two states: null or not null. It either has
data (a value, depending on the datatype), or no data (null),
which is where <column> IS NULL (has no data) or
<column> IS NOT NULL (has data) comes into play.

Null is not the same as empty. An empty string, for example, 
is not equal to null (which is "unknown"), you cannot compare
anything to what you don't know, which is why your comparison
fails.

NULL = NULL fails, so does NULL <> NULL in the strict
sense.


>   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:

You mean the "optimizer", perhaps it should, perhaps it shouldn't. Yet,
your query is not really the smartest, as you should avoid writing
<column> = NULL, as this doesn't make sense.

>   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?

Quite a bit ;-)

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com