Summary

NULL in a nutshell:

NULL means unknown.
Every field or variable that has been created but not initialised is in a NULL state.
To exclude NULLs from a domain or column, add “NOT NULL” after the type name.
To find out if A is NULL, use “A IS [NOT] NULL”.
Assigning NULL is done like assigning values: with “A = NULL” or an insert list.
To find out if A and B are the same, with the understanding that all NULLs are the same and different from anything else, use “A IS [NOT] DISTINCT FROM B” in Firebird 2 and up. In earlier versions the tests are:
```
// equality:
A = B or A is null and B is null
```
```
// inequality:
A <> B
or A is null and B is not null
or A is not null and B is null
```
In Firebird 2 and up you can use NULL literals in just about every situation where a regular value is also allowed. In practice this mainly gives you a lot more rope to hang yourself.
Most of the time, NULL operands make the entire operation return NULL. Noteworthy exceptions are:
- “NULL or true” evaluates to true;
- “NULL and false” evaluates to false.
The IN, ANY|SOME and ALL predicates may (but do not always) return NULL if either the left-hand side expression or a list/subresult element is NULL.
The [NOT] EXISTS predicate never returns NULL. The [NOT] SINGULAR predicate never returns NULL in Firebird 2.1 and up. It is broken in all previous versions.
In aggregate functions only non-NULL fields are involved in the computation. Exception: COUNT(*).
In ordered sets, NULLs are placed...
- 1.0: At the bottom;
- 1.5: At the bottom, unless NULLS FIRST specified;
- 2.0: At the “small end” (top if ascending, bottom if descending), unless overridden by NULLS FIRST/LAST.
If a WHERE or HAVING clause evaluates to NULL, the row is not included in the result set.
If the test expression of an IF statement is NULL, the THEN block is skipped and the ELSE block executed.
A CASE statement returns NULL:
- If the selected result is NULL.
- If no matches are found (simple CASE) or no conditions are true (searched CASE) and there is no ELSE clause.
In a simple CASE statement, “CASE <null_expr>” does not match “WHEN <null_expr>”.
If the test expression of a WHILE statement evaluates to NULL, the loop is not (re)entered.
A FOR statement is not exited when NULLs are received. It continues to loop until either all the rows have been processed or it is interrupted by an exception or a loop-breaking PSQL statement.
In Primary Keys, NULLs are never allowed.
In Unique Keys and Unique Indices, NULLs are
- not allowed in Firebird 1.0;
- allowed (even multiple) in Firebird 1.5 and higher.
In Foreign Key columns, multiple NULLs are allowed.
If a CHECK constraint evaluates to NULL, the input is
- rejected under Firebird 1.5 and earlier;
- accepted under Firebird 2.0 and higher.
SELECT DISTINCT considers all NULLs equal: in a single-column select, at most one is returned.
UDFs sometimes convert NULL <–> non-NULL in a seemingly random manner.
The COALESCE and *NVL functions can convert NULL to a value.
The NULLIF family of functions can convert values to NULL.
If you add a NOT NULL column without a default value to a populated table, all the entries in that column will be NULL upon creation. Most clients however – including Firebird's isql tool – will falsely report them as zeroes (0 for numerical fields, '' for string fields, etc.)
If you change a column's datatype to a NOT NULL domain, any existing NULLs in the column will remain NULL. Again most clients – including isql – will show them as zeroes.

Remember, this is how NULL works in Firebird SQL. There may be (at times subtle) differences with other RDBMSes.