4.1.4. NULL in Expressions

NULL is not a value in SQL, but a state indicating that the value of the element either is unknown or it does not exist. It is not a zero, nor a void, nor an “empty string”, and it does not act like any value.

When you use NULL in numeric, string or date/time expressions, the result will always be NULL. When you use NULL in logical (Boolean) expressions, the result will depend on the type of the operation and on other participating values. When you compare a value to NULL, the result will be unknown.

Important to Note

NULL means NULL but, in Firebird, the logical result unknown is also represented by NULL.

Expressions Returning NULL

Expressions in this list will always return NULL:

  1. 1 + 2 + 3 + NULL
  2. 'Home ' || 'sweet ' || NULL
  3. MyField = NULL
  4. MyField <> NULL
  5. NULL = NULL
  6. not (NULL)

If it seems difficult to understand why, remember that NULL is a state that stands for “unknown”.

NULL in Logical Expressions

It has already been shown that NOT (NULL) results in NULL. The interaction is a bit more complicated for the logical AND and logical OR operators:

  1. NULL or false = NULL
  2. NULL or true = true
  3. NULL or NULL = NULL
  4. NULL and false = false
  5. NULL and true = NULL
  6. NULL and NULL = NULL

Up to and including Firebird 2.5.x, there is no implementation for a logical (Boolean) data type — that is coming in Firebird 3. However, there are logical expressions (predicates) that can return true, false or unknown.

Examples

  1. (1 = NULL) or (1 <> 1) -- returns NULL
  2. (1 = NULL) or (1 = 1) -- returns TRUE
  3. (1 = NULL) or (1 = NULL) -- returns NULL
  4. (1 = NULL) and (1 <> 1) -- returns FALSE
  5. (1 = NULL) and (1 = 1) -- returns NULL
  6. (1 = NULL) and (1 = NULL) -- returns NULL