A database gotcha - null doesn't equal anything

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.