Virtually anyone who has written a SQL query will have encountered NULL column items. All of the text books repeat the same sermon:- NULL is not equal to anything, not even itself.

Virtually anyone who has written a SQL query will have encountered NULL column items. All of the text books repeat the same sermon:-

NULL is not equal to anything, not even itself.

..which of course means that if a field is not set (i.e. is NULL) it will be ignored by a query such as:-

select name,'good' from player where score >= 60 UNION ALL select name,'poor' from player where score < 60 ;

At first glance the above query would appear to return all players, poor and good. If however a player's score value is not set, the query will not return that player. If the query is modified thus:-

select name,'good' from player where score >= 60 UNION ALL select name,'poor' from player where score < 60 UNION ALL select name,'unknown' from player where score is NULL ;

..all players are returned. Note the use of IS NULL to ensure that rows with an undefined score are returned.

Name	Rating
Jason	good
Phineas	poor
Medea	unknown

However it is also in a sense correct to say:-

NULL is not not equal to anything

Confused?

Consider the following simple table, holding the name, year of coming to office, and current status of the President of the United Status (or POTUS):-

CREATE TABLE IF NOT EXISTS `potus` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255), `year` SMALLINT, `status` varchar(20), PRIMARY KEY (`id`) ) ENGINE=innodb DEFAULT CHARSET=utf8;

We then populate the table with the holders of that particular job over the past century:-

insert into potus (name,year,status) VALUES ('Barack Obama', 2009,'current');

insert into potus (name,year,status) VALUES ('George W Bush', 2001,'former');

insert into potus (name,year) VALUES ('Bill Clinton', 1993);

insert into potus (name,year,status) VALUES ('George H Bush', 1989,'former');

insert into potus (name,year,status) VALUES ('Ronald Reagan', 1981,'deceased');

insert into potus (name,year,status) VALUES ('Jimmy Carter', 1977,'former');

insert into potus (name,year,status) VALUES ('Gerald Ford', 1974,'deceased');

insert into potus (name,year,status) VALUES ('Richard Nixon', 1969,'deceased');

insert into potus (name,year,status) VALUES ('Lyndon Johnson', 1963,'deceased');

insert into potus (name,year,status) VALUES ('John Kennedy', 1961,'deceased');

insert into potus (name,year,status) VALUES ('Dwight Eisenhower', 1953,'deceased');

insert into potus (name,year,status) VALUES ('Harry S Truman', 1945,'deceased');

insert into potus (name,year,status) VALUES ('Franklin Roosevelt',1933,'deceased');

insert into potus (name,year,status) VALUES ('Herbert Hoover', 1929,'deceased');

insert into potus (name,year,status) VALUES ('Calvin Coolidge', 1923,'deceased');

insert into potus (name,year,status) VALUES ('Warren Harding', 1921,'deceased');

insert into potus (name,year,status) VALUES ('Woodrow Wilson', 1913,'deceased');

Keen observers will note that an error was made when inserting the 42nd President, a Mr Clinton; his current status was not inserted into the table, and is thus NULL.

The following query thus, as you would expect, fails to return Mr Clinton, given that his status is not equal to 'current' or 'former':-

select name from potus where status IN ('current','former');

However you may think that this query, to return all presidents who are not deceased, would return Mr Clinton:-

select name, year from potus where status !='deceased' order by year desc ;

... but it does not. Mr Clinton's status is NULL, and so it is not not equal to 'deceased'. NULL will not work with any regular comparitor (equals, not equals, less than etc).

The query produces:-

Name	Year
Barack Obama	2009
George W Bush	2001
George H Bush	1989
Jimmy Carter	1977

The following query returns any live presidents, plus any whose health is undefined:-

select name,year from potus where (status !='deceased' or status is NULL) order by year desc ;

Name	Year
Barack Obama	2009
George W Bush	2001
Bill Clinton	1993
George H Bush	1989
Jimmy Carter	1977