SQL, NULL and the 42nd President

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