meta data for this page
  •  

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:
 // 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.