NULL Support In Firebird SQL

In a column or domain definition, you can specify that only non-NULL values may be entered by adding NOT NULL to the definition:

Adding a NOT NULL column to an existing table that already contains records requires special care.This operation will be discussed in detail in the section [nullguide-alter-pop-tables].

If you want to know whether a variable, field or other expression is NULL, use the following syntax:

Examples:

Do not use “…​ = NULL” to test for nullness.This syntax is illegal in Firebird versions up to 1.5.n, and gives the wrong result in Firebird 2 and up: it returns NULL no matter what you compare.This is by design, incidentally, and in that sense it’s not really wrong — it just doesn’t give you what you want.The same goes for “…​ <> NULL”, so don’t use that either;use IS NOT NULL instead.

IS NULL and IS NOT NULL always return true or false;they never return NULL.

Setting a field or variable to NULL is done with the ‘=’ operator, just like assigning values.You can also include NULL in an insert list or use it as input parameter to a stored procedure (both are special types of assignments).

Remember:

In Firebird 2 and higher only, you can test for the null-encompassing equality of two expressions with “IS [NOT] DISTINCT FROM”:

Fields, variables and other expressions are considered:

[NOT] DISTINCT always returns true or false, never NULL or something else.

With earlier Firebird versions, you have to write special code to obtain the same information.This will be discussed later.

The ability to use NULL literals depends on your Firebird version.

The operations in this list always return NULL:

If you have difficulty understanding why, remember that NULL means “unknown”.You can also look at the following table where per-case explanations are provided.In the table we don’t write NULL in the expressions (as said, this is often illegal);instead, we use two entities A and B that are both NULL.A and B may be fields, variables, or even composite subexpressions — as long as they’re NULL, they’ll all behave the same in the enclosing expressions.

Here is the complete list of math and string operators that return NULL if at least one operand is NULL:

The explanations all follow the same pattern: if A is unknown, you can’t tell if it’s greater than B;if string S1 is unknown, you can’t tell if it contains S2;etcetera.

Using LIKE with a NULL escape character would crash the server in Firebird versions up to and including 1.5.This bug was fixed in v.1.5.1.From that version onward, such a statement will yield an empty result set.

All the operators examined so far return NULL if any operand is NULL.With boolean operators, things are a bit more complex:

In version 2.5 and earlier, Firebird SQL doesn’t have a boolean data type;nor are true and false existing constants.In the leftmost column of the explanatory table below, “true” and “false” represent expressions (fields, variables, composites…​) that evaluate to true/false.

All these results are in accordance with boolean logic.The fact that you don’t need to know X's value to compute “X or true” and “X and false” is also the basis of a feature found in various programming languages: short-circuit boolean evaluation.

The above results can be generalised as follows for expressions with one type of binary boolean operator (and | or) and any number of operands:

Or, shorter:

If you have trouble remembering which constant rules which operation, look at the second letter: tRue prevails with oR — fAlse with And.

The short-circuit results obtained above may lead you to the following ideas:

How is this reflected in Firebird SQL?Well, I’m sorry I have to inform you that despite this compelling logic — and the analogy with the boolean results discussed above — the following expressions all resolve to NULL:

So much for consistency.

The following built-in functions return NULL if at least one argument is NULL:

The following two directives crash a Firebird 1.5.n or lower server if given a NULL argument.In Firebird 2, they treat NULL as the value 0:

This new Firebird 2 directive returns an empty set if any argument is NULL:

In new code, use ROWS, not FIRST and SKIP.

The IN predicate compares the expression on its left-hand side to a number of expressions passed in the argument list and returns true if a match is found.NOT IN always returns the opposite of IN.Some examples of its use are:

The list can also be generated by a one-column subquery:

If the list is empty (this is only possible with a subquery), IN always returns false and NOT IN always returns true, even if the test expression is NULL.This makes sense: even if a value is unknown, it is certain not to occur in an empty list.

If the list is not empty and the test expression — called “A” in the examples below — is NULL, the following predicates will always return NULL, regardless of the expressions in the list:

The first result can be understood by writing out the entire expression as a disjunction (OR-chain) of equality tests:

which, if A is NULL, boils down to

which is NULL.

The nullness of the second predicate follows from the fact that “not (NULL)” equals NULL.

If A has a proper value, but the list contains one or more NULL expressions, things become a little more complicated:

Needless to say, if neither A nor any list expression is NULL, the result is always as expected and can only be true or false.

The table below shows all the possible results for IN and NOT IN.To use it properly, start with the first question in the left column.If the answer is No, move on to the next line.As soon as an answer is Yes, read the results from the second and third columns and you’re done.

In many contexts (e.g. within IF and WHERE clauses), a NULL result behalves like false in that the condition is not satisfied when the test expression is NULL.On the one hand this is convenient for cases where you might expect false but NULL is returned: you simply won’t notice the difference.On the other hand, this may also lead you to expect true when the expression is inverted (using NOT) and this is where you’ll run into trouble.In that sense, the most “dangerous” case in the above table is when you use an expression of the type “A NOT IN (<list>)”, with A indeed not present in the list (so you’d expect a clear true result), but the list happens to contain one or more NULLs.

The IN() predicate is often used in CHECK constraints.In that context, NULL expressions have a surprisingly different effect in Firebird versions 2.0 and up.This will be discussed in the section [nullguide-check-constraints].

Firebird has two quantifiers that allow you to compare a value to the results of a subselect:

With ANY, SOME and ALL you provide the comparison operator yourself.This makes it more flexible than IN, which only supports the (implicit) ‘=’ operator.On the other hand, ANY, SOME and ALL only accept a subselect as an argument;you can’t provide an explicit list, as with IN.

Valid operators are =, !=, <, >, =<, ⇒ and all their synonyms.You can’t use LIKE, CONTAINING, IS DISTINCT FROM, or any other operators.

Some usage examples:

If the subselect returns an empty set, ALL returns true and ANY|SOME return false, even if the left-hand side expression is NULL.This follows from the definitions and the rules of formal logic.(Math-heads will already have noticed that ALL is equivalent to the universal (“A”) quantifier and ANY|SOME to the existential (“E”) quantifier.)

For non-empty sets, you can write out “A <op> {ANY|SOME} (<subselect>)” as

with <op> the operator used and E1, E2 etc. the items returned by the subquery.

Likewise, “A <op> ALL (<subselect>)” is the same as

This should look familiar.The first writeout is equal to that of the IN predicate, except that the operator may now be something other than ‘=’.The second is different but has the same general form.We can now work out how nullness of A and/or nullness of subselect results affect the outcome of ANY|SOME and ALL.This is done in the same way as earlier with IN, so instead of including all the steps here we will just present the result tables.Again, read the questions in the left column from top to bottom.As soon as you answer a question with “Yes”, read the result from the second column and you’re done.

If you think these results look a lot like what we saw with IN(), you’re right: with the ‘=’ operator, ANY is the same as IN.In the same way, “<> ALL” is equivalent to NOT IN.

The EXISTS and SINGULAR predicates return information about a subquery, usually a correlated subquery.You can use them in WHERE, HAVING, CHECK, CASE, IF and WHILE clauses (the latter two are only available in PSQL, Firebird’s stored procedure and trigger language).

EXISTS tells you whether a subquery returns at least one row of data.Suppose you want a list of farmers who are also landowners.You could get one like this:

This query returns the names of all farmers who also figure in the Landowners table.The EXISTS predicate returns true if the result set of the subselect contains at least one row.If it is empty, EXISTS returns false. EXISTS never returns NULL, because a result set always either has rows, or hasn’t.Of course the subselect’s search condition may evolve to NULL for certain rows, but that doesn’t cause any uncertainty: such a row won’t be included in the subresult set.

NOT EXISTS always returns the opposite of EXISTS: false or true, never NULL.NOT EXISTS returns false immediately if it gets a true result on the subquery’s search condition.Before returning true it must step through the entire set.

SINGULAR is an InterBase/Firebird extension to the SQL standard.It is often described as returning true if exactly one row in the subquery meets the search condition.By analogy with EXISTS this would make you expect that SINGULAR too will only ever return true or false.After all, a result set has either exactly 1 row or a different number of rows.Unfortunately, all versions of Firebird up to and including 2.0 have a bug that causes NULL results in a number of cases.The behaviour is pretty inconsistent, but at the same time fully reproducible.For instance, on a column A containing (1, NULL, 1), a SINGULAR test with subselect “A=1” returns NULL, but the same test on a column with (1, 1, NULL) returns false.Notice that only the insertion order is different here!

To make matters worse, all versions prior to 2.0 sometimes return NULL for NOT SINGULAR where false or true is returned for SINGULAR.In 2.0, this at least doesn’t happen anymore: it’s either false vs. true or twice NULL.

The code has been fixed for Firebird 2.1; from that version onward SINGULAR will return:

Whether the other rows yield false, NULL or a combination thereof, is irrelevant.

NOT SINGULAR will always return the opposite of SINGULAR (as is already the case in 2.0).

In the meantime, if there’s any chance that the search condition may evolve to NULL for one or more rows, you should always add an IS NOT NULL condition to your [NOT] SINGULAR clauses, e.g.like this:

A GROUP BY clause doesn’t change the aggregate function logic described above, except that it is now applied to each group individually rather than to the result set as a whole.Suppose you have a table Employee, with fields Dept and Salary which both allow NULLs, and you run this query:

The result may look like this (the row where Dept is <null> may be at the top or bottom, depending on your Firebird version):

<null> 219465.19000 266643.00100 155262.50110 130442.81115 13480000.00120 <null>121 110000.00123 390500.00

A GROUP BY clause can be used to report the frequencies with which values occur in a table.In that case you use the same field name several times in the query statement.Let’s say you have a table TT with a column A whose contents are { 3, 8, NULL, 6, 8, -1, NULL, 3, 1 }.To get a frequencies report, you could use:

which would give you this result:

Oops — something went wrong with the NULL count, but what? Remember that COUNT(FieldName) skips all NULL fields, so with COUNT(A) the count of the <null> group can only ever be 0.Reformulate your query like this:

and the correct value will be returned (in casu 2).

HAVING clauses can place extra restrictions on the output rows of an aggregate query — just like WHERE clauses do in record-by-record queries.A HAVING clause can impose conditions on any output column or combination of columns, aggregate or not.

As far as NULL is concerned, the following two facts are worth knowing (and hardly surprising, I would guess):

For instance, adding the following clause to the example query from the “GROUP BY” paragraph:

will prevent the first row from being output, whereas this one:

suppresses the sixth row (the one with Dept = 120).

If the test expression of an IF statement resolves to NULL, the THEN clause is skipped and the ELSE clause — if present — executed.In other words, NULL and false have the same effect in this context.So in situations where you would logically expect false but NULL is returned, no harm will be done.However, we’ve already seen examples of NULL being returned where you would expect true, and that does affect the flow of the code!

Below are some examples of the seemingly paradoxical (but perfectly correct) results you can get if NULLs creep into your IF statements.

So how should you set up equality tests that do give the logical result under all circumstances, even with NULL operands?In Firebird 2 you can use DISTINCT, as already shown (see Testing DISTINCTness). With earlier versions, you’ll have to write some more code.This is discussed in the section [nullguide-testing-equality], later on in this guide.For now, just remember that you have to be very careful with IF conditions that may resolve to NULL.

Another aspect you shouldn’t forget is the following: a NULL test expression may behave like false in an IF condition, but it doesn’t have the value false.It’s still NULL, and that means that its inverse will also be NULL — not “true”.As a consequence, inverting the test expression and swapping the THEN and ELSE blocks may change the behaviour of the IF statement.In binary logic, where only true and false can occur, such a thing could never happen.

To illustrate this, let’s refactor the last example:

Of course, as long as no operand in the test expression can ever be NULL, you can happily formulate your IF statements like above.Also, refactoring by inverting the test expression and swapping the THEN and ELSE blocks will always preserve the functionality, regardless of the complexity of the expressions — as long as they aren’t NULL.What’s especially treacherous is when the operands are almost always non-NULL, so in the vast majority of cases the results will be correct.In such a situation those rare NULL cases may go unnoticed for a long time, silently corrupting your data.

Firebird introduced the CASE construct in version 1.5, with two syntactic variants.The first one is called the simple syntax:

This one works more or less like a Pascal case or a C switch construct: <expression> is compared to <exp1>, <exp2> etc., until a match is found, in which case the corresponding result is returned.If there is no match and there is an ELSE clause, <defaultresult> is returned.If there is no match and no ELSE clause, NULL is returned.

It is important to know that the comparisons are done with the ‘=’ operator, so a null <expression> will not match a null <expN>.If <expression> is NULL, the only way to get a non-NULL result is via the ELSE clause.

It is OK to specify NULL (or any other valid NULL expression) as a result.

The second, or searched syntax is:

Here, the <conditionN>s are tests that give a ternary boolean result: true, false, or NULL.Once again, only true is good enough, so a condition like “A = 3” — or even “A = null” — is not satisfied when A is NULL.Remember though that “IS [NOT] NULL” never returns NULL: if A is NULL, the condition “A is null” returns true and the corresponding <resultN> will be returned.In Firebird 2+ you can also use “IS [NOT] DISTINCT FROM” in your conditions — this operator too will never return NULL.

When evaluating the condition of a WHILE loop, NULL has the same effect as in an IF statement: if the condition resolves to NULL, the loop is not (re)entered — just as if it were false.Again, watch out with inversion using NOT: a condition like

will skip the loop block if Counter is NULL, which is probably what you want, but:

will also skip if Counter is NULL.Maybe this is also exactly what you want — just be aware that these seemingly complementary tests both exclude NULL counters.

To avoid any possible confusion, let us emphasise here that FOR loops in Firebird PSQL have a totally different function than WHILE loops, or for loops in general programming languages.Firebird FOR loops have the form:

and they will keep executing the code block until all the rows from the result set have been retrieved, unless an exception occurs or a BREAK, LEAVE or EXIT statement is encountered.Fetching a NULL, or even row after row filled with NULLs, does not terminate the loop!