NULL IS NOT NULL (or “how to say ‘none of the above’ in SQL”)

Sometimes you do not have enough information to fill out a form completely. SQL uses the keyword NULL to represent “Unknown” or “No Value”. (This is different from the meaning of NULL in programming languages such as C.) For example, if you are inserting a record for Joni Mitchell into the table of composers and you do not know Joni Mitchell’s address, you might execute the following:

INSERT INTO composers (id, name, address) VALUES (5, 'Mitchell', NULL);

INSERT INTO composers (id, name) VALUES (5, 'Mitchell');

-- Data of any data type may contain NULL.
-- For example, a column of type INTEGER may contain not
-- only valid integer values, but also NULL.

-- Set up for experiments...
CREATE TABLE table1 (x INTEGER, name CHAR(30));

-- The value NULL means "there is no value".
-- NULL is not the same as zero, or an empty string.
-- (It is also not a pointer value, as it is in
-- programming languages such as C.)
-- To help show this, insert 3 rows, one of which has
-- "normal" values, one of which has a 0 and an empty string,
-- and one of which has two NULL values.
INSERT INTO table1 (x, name) VALUES (2, 'Ludwig Von Beethoven');
INSERT INTO table1 (x, name) VALUES (0, '');
INSERT INTO table1 (x, name) VALUES (NULL, NULL);
-- This returns only the row containing 0,
-- not the row containing NULL.
SELECT * FROM table1 WHERE x = 0;
-- This returns only the row containing the empty string,
-- not the row containing NULL.
SELECT * FROM table1 WHERE name = '';

-- As expected, NULL does not match other values.
-- However NULL does not match even itself.
-- (A mathematician would say that NULL violates the
-- reflexive property "a = a".) SELECT * FROM table1 WHERE x = x;

-- Since NULL does not equal NULL, what will the following query return?
SELECT * FROM table1 WHERE x <> x;

-- Similarly, although you might think that the
-- expression below is always true, it is actually
-- always false.
SELECT * FROM table1 WHERE NULL IN (NULL, 2);

-- The result set will contain 2 (since 2 is in
-- the set (NULL, 2)), but the result set will
-- not contain NULL. SELECT * FROM table1 WHERE x IN (NULL, 2);

-- But suppose that you *want* to find all the records that
-- have NULL values. How to do that if I it is not possible to say
... = NULL?
SELECT * FROM table1 WHERE x IS NULL;
-- And the opposite query is ...
SELECT * FROM table1 WHERE x IS NOT NULL;

-- Set up for more experiments...
CREATE TABLE parent (id INTEGER, name CHAR(20));
CREATE TABLE children (id INTEGER, name CHAR(12), parent_id INT);
INSERT INTO parent (id, name) VALUES (1, 'Smith');
INSERT INTO children (id, name, parent_id) VALUES (11, 'Smith child', 1);
INSERT INTO children (id, name, parent_id) VALUES (131, 'orphan', NULL);
INSERT INTO parent (id, name) VALUES (NULL, 'Has Null');

-- Since NULL <> NULL, if a "parent" record has NULL and a "child"
-- record has NULL, the child's value will not match the parent's value.
-- This result set will contain 'Smith', but not 'Has Null'.
SELECT p.name FROM parent p, children c WHERE c.parent_id = p.id;

-- Note that a row that contains nothing but a -- single NULL is still a row.
-- In the following query, an EXISTS clause is used. -- It evaluates to TRUE if the subquery returns -- any rows. Even a row that contains nothing but a -- single NULL value is still a row, and so if the -- subquery returns a single NULL the EXISTS clause -- still evaluates to TRUE.
-- Even though the subquery below returns NULL rather than a name -- or ID, the EXISTS expression evaluates to TRUE, and Smith is printed. SELECT name FROM parent p WHERE EXISTS(SELECT NULL FROM children c WHERE c.parent_id = p.id);

-- Now that you recognize that NULL <> NULL,
-- the following may confuse you since it breaks the pattern.
-- Contrary to what you might expect, the UNIQUE keyword
-- DOES filter out multiple NULL values.
INSERT INTO table1 (x, name) VALUES (NULL, 'any name');
-- Now the table has more than one row in which x is NULL,
-- but a query with UNIQUE nonetheless returns only a
-- single NULL value.
SELECT DISTINCT x FROM table1;
-- You may be interested to know that a UNIQUE index
-- will allow only a single NULL value. (Note that a primary key -- will not allow any NULL values.)