FirebirdSQL logo
 Data Types and SubtypesData Definition (DDL) Statements 
Searched CASE
Syntax
CASE
  WHEN <bool_expr> THEN <result>
  [WHEN <bool_expr> THEN <result> ...]
  [ELSE <defaultresult>]
END

The bool_expr expression is one that gives a ternary logical result: TRUE, FALSE or NULL.The first expression to return TRUE determines the result.If no expressions return TRUE, defaultresult from the optional ELSE clause is returned as the result.If no expressions return TRUE and there is no ELSE clause, the result will be NULL.

As with the simple CASE construct, the result need not be a literal value: it might be a field or variable name, a compound expression, or be NULL.

Example
CANVOTE = CASE
  WHEN AGE >= 18 THEN 'Yes'
  WHEN AGE < 18 THEN 'No'
  ELSE 'Unsure'
END

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