FirebirdSQL logo
 Data Types and SubtypesData Definition (DDL) Statements 

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

In SQL, the logical result unknown is also represented by NULL.

Consult theFirebird Null Guidefor more in-depth coverage of Firebird’s NULL behaviour.

Expressions Returning NULL

Expressions in this list will always return NULL:

1 + 2 + 3 + NULL
'Home ' || 'sweet ' || NULL
MyField = NULL
MyField <> NULL
NULL = NULL
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:

NULL or false  → NULL
NULL or true   → true
NULL or NULL   → NULL
NULL and false → false
NULL and true  → NULL
NULL and NULL  → NULL
Tip

As a basic rule-of-thumb, if substituting TRUE for NULL produces a different result than substituting FALSE, the outcome of the original expression is unknown, or NULL.
Examples
(1 = NULL) or (1 <> 1)    -- returns NULL
(1 = NULL) or FALSE       -- returns NULL
(1 = NULL) or (1 = 1)     -- returns TRUE
(1 = NULL) or TRUE        -- returns TRUE
(1 = NULL) or (1 = NULL)  -- returns NULL
(1 = NULL) or UNKNOWN     -- returns NULL
(1 = NULL) and (1 <> 1)   -- returns FALSE
(1 = NULL) and FALSE      -- returns FALSE
(1 = NULL) and (1 = 1)    -- returns NULL
(1 = NULL) and TRUE       -- returns NULL
(1 = NULL) and (1 = NULL) -- returns NULL
(1 = NULL) and UNKNOWN    -- returns NULL

Subqueries

A subquery is a special form of expression that is a query embedded within another query.Subqueries are written in the same way as regular SELECT queries, but they must be enclosed in parentheses.Subquery expressions can be used in the following ways:

  • To specify an output column in the SELECT list

  • To obtain values or conditions for search predicates (the WHERE, HAVING clauses).

  • To produce a set that the enclosing query can select from, as though were a regular table or view.Subqueries like this appear in the FROM clause (derived tables) or in a Common Table Expression (CTE)