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, NULLoperands 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.
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.
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.