<< Testing for NULL and equality in practice | Firebird Null Guide | A: NULL-related bugs in Firebird >>

Summary

NULL in a nutshell:

• NULL means unknown.
• 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:

• 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.

back to top of page
<< Testing for NULL and equality in practice | Firebird Null Guide | A: NULL-related bugs in Firebird >>