FirebirdSQL logo

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)

Correlated Subqueries

A subquery can be correlated.A query is correlated when the subquery and the main query are interdependent.To process each record in the subquery, it is necessary to fetch a record in the main query, i.e. the subquery fully depends on the main query.

Sample Correlated Subquery
SELECT *
FROM Customers C
WHERE EXISTS
  (SELECT *
   FROM Orders O
   WHERE C.cnum = O.cnum
     AND O.adate = DATE '10.03.1990');

When subqueries are used to get the values of the output column in the SELECT list, a subquery must return a scalar result (see below).