I recently spent a long time debugging a problem where a database query was returning fewer rows than I expected. The cause was a well-known fact, but one that's easy to forget about: a "not equals" comparison to NULL is always false. An example makes this clear...
Create a table with a couple of columns:
CREATE TABLE test
(
id integer NOT NULL,
name character varying(10),
CONSTRAINT test_pkey PRIMARY KEY (id)
)
Insert a few rows into the table; one of the rows includes a NULL value:
INSERT INTO test VALUES (1, 'abc');
INSERT INTO test VALUES (2, 'def');
INSERT INTO test VALUES (3, null);
Suppose we'd like to find all the rows where the name isn't 'abc'. It seems like this query would do it:
SELECT * FROM test WHERE name <> 'abc'
But that doesn't work. The query returns only the first row. This query solves the problem:
SELECT * FROM test WHERE COALESCE(name, '') <> 'abc'
I executed the above example in PostgreSQL 9.2, but the same principle applies to many databases.
