The problematic NULLs

(1)  The base principal
anything OP NULL => NULL
NULL OP anything => NULL
NULL doesn't equal anything including NULL
NULL = NULL => NULL
NULL <> NULL => NULL
NULL IS NULL => true
NULL IS NOT NULL => false
SELECT NULL IS DISTINCT FROM NULL; => false

(2)  Explicitly distinct NULL
CREATE TABLE disttest (x INTEGER, y INTEGER);
INSERT INTO disttest VALUES (1, 1), (2, 3), (NULL, NULL);
SELECT * FROM disttest where x IS NOT DISTINCT FROM y; =>
x          |     y
--------+--------
1         |    1
(null)  | (null)

(3) NULLs are treated as equal for ordering purposes
---
WITH ordertest AS (
    SELECT NULL
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 1
    UNION ALL
    SELECT NULL
)
SELECT * FROM ordertest
ORDER BY 1 ; -- NULLS FIRST
?column?
----------
1
2
(null)
(null)

(4) Unique Indexes Treat NULLs as Unequal
--
CREATE TABLE uniqtest (x INTEGER);
CREATE UNIQUE INDEX i_uniqtest ON uniqtest (x);
INSERT INTO uniqtest VALUES (1), (NULL), (NULL);
SELECT * FROM uniqtest;
x
--------
1
(null)
(null)

(5) NULLs and Aggregates
--
CREATE TABLE aggtest (x INTEGER);
INSERT INTO aggtest VALUES (7), (8), (NULL);
SELECT COUNT(*), COUNT(x), SUM(x), MIN(x), MAX(x), AVG(x) FROM aggtest;
count | count | sum | min | max | avg
--------+--------+-------+-----+------+--------------------
3        | 2         | 15    | 7      | 8       | 7.5000000000000000
DELETE FROM aggtest;
SELECT COUNT(*), COUNT(x), SUM(x), MIN(x), MAX(x), AVG(x) FROM aggtest;
count | count | sum | min    | max  | avg
-------+--------+-------+--------+--------+--------
0        | 0        | (null) | (null) | (null)  | (null)
The sum of zero rows is NULL.

(6) NULLs and GROUP BY
INSERT INTO aggtest VALUES (7), (8), (NULL), (NULL);
INSERT 0 4
SELECT x, COUNT(*), COUNT(x), SUM(x), MIN(x), MAX(x), AVG(x)
FROM aggtest
GROUP BY x
ORDER BY x;
x | count | count | sum | min | max | avg
--------+-------+-------+--------+--------+--------+--------------------
7 | 1 | 1 | 7 | 7 | 7 | 7.0000000000000000
8 | 1 | 1 | 8 | 8 | 8 | 8.0000000000000000
(null) | 2 | 0 | (null) | (null) | (null) | (null)

(7) COALESCE/NULLIF

(8) 
CREATE TABLE emptytest (x INTEGER);
SELECT (SELECT * from emptytest);
x
--------
(null)
A SELECT with no FROM clause is assumed to return one row.

(9)

Pay attention to  "NOT IN" subquery, "NOT EXISTS" is often the right way.

From postgresql 8.2 manual.
---
expression NOT IN (subquery)
The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand
expression is evaluated and compared to each row of the subquery result. The result of NOT IN is “true”
if only unequal subquery rows are found (including the special case where the subquery returns no rows).
The result is “false” if any equal row is found.
Note that if the left-hand expression yields null, or if there are no equal right-hand values and at least one
right-hand row yields null, the result of the NOT IN construct will be null, not true. This is in accordance
with SQL’s normal rules for Boolean combinations of null values.
As with EXISTS, it’s unwise to assume that the subquery will be evaluated completely.