NULL

NULL is the term used to describe a data field without a value, i.e. the field has been left blank because the information is either not known or not relevant for this record/data set. The NULL value can be stored in text, numeric and date data types.

A relational database is able to store NULL values as data content. A NULL value does not mean numerical zero. For example, a product can have zero sales (0) or unknown sales (<null>), and just because a customer's telephone number is not known (<null>), this does not mean that the customer has no telephone, and he most certainly will not have the telephone number “0”!

A NULL value can occur for the following reasons:

• The value is not yet known, but will be added at a future date.
• The value is not yet available for some reason, e.g. the date of receipt of payment.
• The value is not important, e.g. the credit card expiry date of someone who has paid cash.

Firebird/InterBase® does not use a special byte sequence to indicate a NULL, but administrates this information internally. NULL values can influence query contents considerably, for example, when a column average is calculated. The values filled by the NULL value, i.e. empty fields, are not taken into consideration. A field containing the value 0 is included in the calculation of the average.

Examples from the Firebird 1.5 Quick Start Guide:

• 1 + 2 + 3 + NULL = NULL
• not (NULL) = NULL
• 'Home ' || 'sweet ' || NULL = NULL

if (a = b) then
   MyVariable = 'Equal';
else
   MyVariable = 'Not equal';

After executing this code, MyVariable will be Not equal if both a and b are NULL. The reason is that the expression a = b yields NULL if at least one of them is NULL. In an if…then context, NULL behaves like FALSE. So the then block is skipped, and the else block executed.

if (a <> b) then
   MyVariable = 'Not equal';
else
   MyVariable = 'Equal';

Here, MyVariable will be Equal if a is NULL and b isn't, or vice versa. The explanation is analogous to that of the previous example.

FirstName || ' ' || LastName

will return NULL if either FirstName or LastName is NULL.

Think of NULL as UNKNOWN and all these strange results suddenly start to make sense! If the value of Number is unknown, the outcome of 1 + 2 + 3 + Number is also unknown (and therefore NULL). If the content of MyString is unknown, then so is MyString || YourString (even if YourString is non-NULL). Etcetera.

New to Firebird 2.0: NULLs are now "lowest" for SORTS

NULL is now treated as the lowest possible value for ordering purposes and sets ordered on nullable criteria are sorted accordingly. Thus: .

• for ascending sorts NULLs are placed at the beginning of the result set,
• for descending sorts NULLs are placed at the end of the result set.

Important: In former versions, NULLs were always at the end. If you have client code or PSQL definitions that rely on the legacy NULLs placement, it will be necessary to use the NULLS LAST option in your ORDER BY clauses for ascending sorts.

Please also refer to the Firebird 2.0.4. Release Notes for further information regarding Enhancements to NULL logic in Firebird 2.