Re: UNIQUE and NULL in SQL

>> What is the rationale for having a UNIQUE constraint treat NULLS as
equal? <<

The same model we used for GROUP BY, which puts NULLs into a single group and does not make each one a group of its own. Grouping is different from equality in SQL and does not have the same rules. The property that a grouping on a table with UNIQUE constraints on the unique constraint column(s) will produce groups of cardinality one is fairly useful.

In English that means:

CREATE TABLE Foobar(a INTEGER UNIQUE);

INSERT INTO Foobar VALUES (1);
INSERT INTO Foobar VALUES (2);
INSERT INTO Foobar VALUES (NULL);

SELECT a, COUNT(*) AS occurs
  FROM Foobar;

a occurs