English is currently available.
SQL DISCUSSION
I have a tickets table where assigned_to is NULL for unassigned tickets. WHERE assigned_to = NULL returns nothing, and WHERE assigned_to <> 5 leaves out the unassigned tickets even though NULL is clearly not 5. Worse, WHERE id NOT IN (SELECT parent_id FROM tickets) returns zero rows although I can see tickets that are nobody's parent.
What are the rules for NULL in comparisons, and how should these three queries be written?
NULL means "value unknown", and SQL uses three-valued logic: a comparison is TRUE, FALSE or UNKNOWN. Any ordinary comparison with NULL, including NULL = NULL and NULL <> 5, gives UNKNOWN, and WHERE keeps only rows where the condition is TRUE. That explains the first two queries.
Test for NULL with the dedicated predicates: WHERE assigned_to IS NULL, and for the second query WHERE assigned_to <> 5 OR assigned_to IS NULL.
The NOT IN result follows from the same logic. id NOT IN (1, 2, NULL) means id <> 1 AND id <> 2 AND id <> NULL. The last term is UNKNOWN for every row, and TRUE AND UNKNOWN is UNKNOWN, so no row can ever pass. Your subquery returns a NULL for every top-level ticket, which poisons the whole list.
Use NOT EXISTS, which is not affected: WHERE NOT EXISTS (SELECT 1 FROM tickets c WHERE c.parent_id = t.id), with t as the alias of the outer table. Alternatively filter the subquery with WHERE parent_id IS NOT NULL.
Aggregates treat NULL differently again: they skip it. For a column holding 10, 20 and NULL, COUNT(*) is 3, COUNT(col) is 2, SUM(col) is 30 and AVG(col) is 15, not 10. If a missing value should count as zero, say so explicitly with AVG(COALESCE(col, 0)).
Arithmetic propagates it: price + NULL is NULL, and in most databases so does string concatenation. COALESCE(a, b, c) returns the first non-NULL argument and is the standard tool for supplying defaults.
Not everything treats NULLs as different from each other. GROUP BY and DISTINCT put all NULLs into one group, and UNION removes duplicate NULL rows. ORDER BY has to place them somewhere, and the default position (first or last) differs between database systems, so state it or test it if the order matters.
For a comparison that treats two NULLs as equal, standard SQL has a IS NOT DISTINCT FROM b, supported by PostgreSQL and recent SQL Server versions; MySQL offers the <=> operator for the same purpose.
Much of this pain can be designed out. Declare columns NOT NULL unless a missing value is a real possibility, and avoid using NULL to mean several different things (not yet known, not applicable, zero). Remember that a CHECK constraint passes when its condition is UNKNOWN, so CHECK (qty > 0) still allows NULL unless the column is also NOT NULL.
Unique constraints on nullable columns are handled differently by different products (some allow many NULLs, some only one), and Oracle treats an empty string as NULL, so check the documentation of your database before relying on either behaviour.
