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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
