FirebirdSQL logo
  • Anglaiskeyboard_arrow_down

NULL in operations

As many of us have found out to our chagrin, NULL is contagious: use it in a numerical, string or date/time operation, and the result will invariably be NULL.With boolean operators, the outcome depends on the type of operation and the value of the other operand.

Please remember that in Firebird versions prior to 2.0 it is mostly illegal to use the constant NULL directly in operations or comparisons.Wherever you see NULL in the expressions below, read it as “a field, variable or other expression that resolves to NULL”.In Firebird 2 and above this expression may also be a NULL literal.

Mathematical and string operations

The operations in this list always return NULL:

  • 1 + 2 + 3 + NULL

  • 5 * NULL - 7

  • 'Home ' || 'sweet ' || NULL

  • MyField = NULL

  • MyField <> NULL

  • NULL = 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.

Table 1. Operations on null entities A and B
If A and B are NULL, then: Is: Because:

1 + 2 + 3 + A

NULL

If A is unknown, then 6 + A is also unknown.

5 * A - 7

NULL

If A is unknown, then 5 * A is also unknown.Subtract 7 and you end up with another unknown.

'Home ' || 'sweet ' || A

NULL

If A is unknown, 'Home sweet ' || A is unknown.

MyField = A

NULL

If A is unknown, you can’t tell if MyField has the same value…​

MyField <> A

NULL

...but you also can’t tell if MyField has a different value!

A = B

NULL

With A and B unknown, it’s impossible to know if they are equal.

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

  • +, -, *, and /

  • !=, ~=, and ^= (synonyms of <>)

  • <, ⇐, >, and >=

  • !<, ~<, and ^< (low-precedence synonyms of >=)

  • !>, ~>, and ^> (low-precedence synonyms of ⇐)

  • ||

  • [NOT] BETWEEN

  • [NOT] STARTING WITH

  • [NOT] LIKE

  • [NOT] CONTAINING

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.

docnext count = 29

Boolean operations

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

  • not NULL = NULL

  • NULL or false = NULL

  • NULL or true = true

  • NULL or NULL = NULL

  • NULL and false = false

  • NULL and true = NULL

  • NULL and NULL = NULL

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.

Table 1. Boolean operations on null entity A
If A is NULL, then: Is: Because:

not A

NULL

If A is unknown, its inverse is also unknown.

A or false

NULL

“A or false” always has the same value as A — which is unknown.

A or true

true

“A or true” is always true — A's value doesn’t matter.

A or A

NULL

“A or A” always equals A — which is NULL.

A and false

false

“A and false” is always false — A's value doesn’t matter.

A and true

NULL

“A and true” always has the same value as A — which is unknown.

A and A

NULL

“A and A” always equals A — which is NULL.

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:

Disjunctions (“A or B or C or D or …​”)

  1. If at least one operand is true, the result is true.

  2. Else, if at least one operand is NULL, the result is NULL.

  3. Else (i.e. if all operands are false) the result is false.

Conjunctions (“A and B and C and D and …​”)

  1. If at least one operand is false, the result is false.

  2. Else, if at least one operand is NULL, the result is NULL.

  3. Else (i.e. if all operands are true) the result is true.

Or, shorter:

  • TRUE beats NULL in a disjunction (OR-operation);

  • FALSE beats NULL in a conjunction (AND-operation);

  • In all other cases, NULL wins.

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

More logic (or not)

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

  • 0 times x equals 0 for every x.Hence, even if x's value is unknown, 0 * x is 0.(Note: this only holds if x’s datatype only contains numbers, not `NaN or infinities.)

  • The empty string is ordered lexicographically before every other string.Therefore, S >= '' is true whatever the value of S.

  • Every value equals itself, whether it’s unknown or not.So, although A = B justifiably returns NULL if A and B are different NULL entities, A = A should always return true, even if A is NULL.The same goes for A ⇐ A and A >= A.

    By analogous logic, A <> A should always be false, as well as A < A and A > A.

  • Every string contains itself, starts with itself and is like itself.So, “S CONTAINING S”, “S STARTING WITH S” and “S LIKE S” should always return true.

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:

  • 0 * NULL

  • NULL >= '' and '' ⇐ NULL

  • A = A, A ⇐ A and A >= A

  • A <> A, A < A and A > A

  • S CONTAINING S, S STARTING WITH S and S LIKE S

So much for consistency.

Internal functions and directives

Internal functions

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

  • CAST()

  • EXTRACT()

  • GEN_ID()

  • SUBSTRING()

  • UPPER()

  • LOWER()

  • BIT_LENGTH()

  • CHAR[ACTER]_LENGTH()

  • OCTET_LENGTH()

  • TRIM()

Note
Notes

  • In 1.0.0, EXTRACT from a NULL date would crash the server.Fixed in 1.0.2.

  • If the first argument to GEN_ID is a valid generator name and the second argument is NULL, the named generator keeps its current value.

  • In versions up to and including 2.0, SUBSTRING results are sometimes returned as “false emptystrings”.These strings are in fact NULL, but are described by the server as non-nullable.Therefore, most clients show them as empty strings.See the bugs list for a detailed description.

FIRST, SKIP and ROWS

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:

  • FIRST

  • SKIP

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

  • ROWS

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

Predicates

Predicates are statements about objects that return a boolean result: true, false or unknown (= NULL).In computer code you typically find predicates in places where a yes/no type of decision has to be taken.For Firebird SQL, that means in WHERE, HAVING, CHECK, CASE WHEN, IF and WHILE clauses.

Comparisons such as “x > y” also return boolean results, but they are generally not called predicates, although this is mainly a matter of form.An expression like Greater( x, y ) that does exactly the same would immediately qualify as a predicate.(Mathematicians like predicates to have a name — such as “Greater” or just plain “G” — and a pair of parentheses to hold the arguments.)

Firebird supports the following SQL predicates: IN, ANY, SOME, ALL, EXISTS and SINGULAR.

Note

It is also perfectly defensible to call “IS [NOT] NULL” and “IS [NOT] DISTINCT FROM” predicates, despite the absence of parentheses.But, predicates or not, they have already been introduced and won’t be discussed in this section.

The IN predicate

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:

select RoomNo, Floor from Classrooms where Floor in (3, 4, 5)
delete from Customers where upper(Name) in ('UNKNOWN', 'NN', '')
if ( A not in (MyVar, MyVar + 1, YourVar, HisVar) ) then ...

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

select ID, Name, Class from Students
  where ID in (select distinct LentTo from LibraryBooks)

With an empty list

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.

With a NULL test expression

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:

  • A IN ( Expr1, Expr2, …​, ExprN )

  • A NOT IN ( Expr1, Expr2, …​, ExprN )

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

A=Expr1 or A=Expr2 or ... or A=ExprN

which, if A is NULL, boils down to

NULL or NULL or ... or NULL

which is NULL.

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

With NULLs in the list

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

  • If at least one of the expressions in the list has the same value as A:

    • “A IN( Expr1, Expr2, …​, ExprN )” returns true

    • “A NOT IN( Expr1, Expr2, …​, ExprN )” returns false

    This is due to the fact that “true or NULL” returns true (see above).Or, more general: a disjunction where at least one of the elements is true, returns true even if some other elements are NULL.(Any falses, if present, are not in the way.In a disjunction, true rules.)

  • If none of the expressions in the list have the same value as A:

    • “A IN( Expr1, Expr2, …​, ExprN )” returns NULL

    • “A NOT IN( Expr1, Expr2, …​, ExprN )” returns NULL

    This is because “false or NULL” returns NULL.In generalised form: a disjunction that has only false and NULL elements, returns NULL.

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.

IN() results

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.

Table 1. Results for “A [NOT] IN (<list>)”

Conditions

Results

IN()

NOT IN()

Is the list empty?

false

true

Else, is A NULL?

NULL

NULL

Else, is at least one list element equal to A?

true

false

Else, is at least one list element NULL?

NULL

NULL

Else (i.e. all list elements are non-NULL and unequal to A)

false

true

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.

Caution

Be especially careful if you use NOT IN with a subselect instead of an explicit list, e.g.

A not in ( select Number from MyTable )

If A is not present in the Number column, the result is true if no Number is NULL, but NULL if the column does contain a NULL entry.Please be aware that even in a situation where A is constant and its value is never contained in the Number column, the result of the expression (and therefore your program flow) may still vary over time according to the absence or presence of NULLs in the column.Hours of debugging fun!Of course you can avoid this particular problem simply by adding “where Number is not NULL” to the subselect.
Warning
Bug alert

All Firebird versions before 2.0 contain a bug that causes [NOT] IN to return the wrong result if an index is active on the subselect and one of the following conditions is true:

  • A is NULL and the subselect doesn’t return any NULLs, or

  • A is not NULL and the subselect result set doesn’t contain A but does contain NULL(s).

Please realise that an index may be active even if it has not been created explicitly, namely if a key is defined on A.

Example: Table TA has a column A with values { 3, 8 }.Table TB has a column B containing { 2, 8, 1, NULL }.The expressions:

A [not] in ( select B from TB )

should both return NULL for A = 3, because of the NULL in B.But if B is indexed, IN returns false and NOT IN returns true.As a result, the query

select A from TA where A not in ( select B from TB )

returns a dataset with one record — containing the field with value 3 — while it should have returned an empty set.Other errors may also occur, e.g. if you use “NOT IN” in an IF, CASE or WHILE statement.

As an alternative to NOT IN, you can use “<> ALL”.The ALL predicate will be introduced shortly.

IN() in CHECK constraints

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].

The ANY, SOME and ALL quantifiers

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

  • ALL returns true if the comparison is true for every element in the subselect.

  • ANY and SOME (full synonyms) return true if the comparison is true for at least one element in the 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:

  • select name, income from blacksmiths
  where income > any( select income from goldsmiths )

    (returns blacksmiths who earn more than at least one goldsmith)

  • select name, town from blacksmiths
  where town != all( select distinct town from goldsmiths )

    (returns blacksmiths who live in a goldsmithless town)

  • if ( GSIncome !> some( select income from blacksmiths ) )
  then PoorGoldsmith = 1;
  else PoorGoldsmith = 0;

    (sets PoorGoldsmith to 1 if at least one blacksmith’s income is not less than the value of GSIncome)

Result values

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

A <op> E1 or A <op> E2 or ... or A <op> En

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

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

A <op> E1 and A <op> E2 and ... and A <op> En

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.

Table 1. Results for “A <op> ANY|SOME (<subselect>)”

Conditions

Result

ANY|SOME

Does the subselect return an empty set?

false

Else, is A NULL?

NULL

Else, does at least one comparison return true?

true

Else, does at least one comparison return NULL?

NULL

Else (i.e. all comparisons return false)

false

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.

Warning
Bug alert (revisited)

In versions before 2.0, “= ANY” suffers from the same bug as IN.Under the “right” circumstances, this can lead to wrong results with expressions of the type “NOT A = ANY( …​ )”.

On the bright side, “<> ALL” is not affected and will always return the right result.

Table 2. Results for “A <op> ALL (<subselect>)”

Conditions

Result

ALL

Does the subselect return an empty set?

true

Else, is A NULL?

NULL

Else, does at least one comparison return false?

false

Else, does at least one comparison return NULL?

NULL

Else (i.e. all comparisons return true)

true
Warning
ALL bug

Although “<> ALL” always works as it should, ALL should nevertheless be considered broken in all pre-2.0 versions of Firebird: with every operator other than “<>”, wrong results may be returned if an index is active on the subselect — with or without NULLs around.
Note

Strictly speaking, the second question in both tables (“is A NULL?”) is redundant and can be dropped.If A is NULL, all the comparisons return NULL, so that situation will be caught a little later.And while we’re at it, we could drop the first question too: the “empty set” situation is just a special case of the final “else”.The whole thing then once again boils down to “true beats NULL beats false” in disjunctions (ANY|SOME) and “false beats NULL beats true” in conjunctions (ALL).

The reason we included those questions is convenience: you can see if a set is empty at a glance, and it’s also easier to check if the left-hand side expression is NULL than to evaluate each and every comparison result.But do feel free to skip them, or to skip just the second.Do not, however, skip the first question and start with the second: this will lead to a wrong conclusion if the set is empty!

EXISTS and SINGULAR

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

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:

SELECT Farmer FROM Farms WHERE EXISTS
  (SELECT * FROM Landowners
   WHERE Landowners.Name = Farms.Farmer)

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.

Note

In reality, the subselect doesn’t return a result set at all.The engine simply steps through the Landowners records one by one and applies the search condition.If it evaluates to true, EXISTS returns true immediately and the remaining records aren’t checked.If it evaluates to false or NULL, the search continues.If all the records have been searched and there hasn’t been a single true result, EXISTS returns false.

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

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:

  • false if the search condition is never true (this includes the empty-set case);

  • true if the search condition is true for exactly 1 row;

  • false if the search condition is true for more than 1 row.

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:

... SINGULAR( SELECT * from MyTable
              WHERE MyField > 38
              AND MyField IS NOT NULL )

Searches

If the search condition of a SELECT, UPDATE or DELETE statement resolves to NULL for a certain row, the effect is the same as if it had been false.Put another way: if the search expression is NULL, the condition is not met, and consequently the row is not included in the output set (or is not updated/deleted).

Note

The search condition or search expression is the WHERE clause minus the WHERE keyword itself.

Some examples (with the search condition in boldface):

SELECT Farmer, Cows FROM Farms WHERE Cows > 0 ORDER BY Cows

The above statement will return the rows for farmers that are known to possess at least one cow.Farmers with an unknown (NULL) number of cows will not be included, because the expression “NULL > 0” returns NULL.

SELECT Farmer, Cows FROM Farms WHERE NOT (Cows > 0) ORDER BY Cows

Now, it’s tempting to think that this will return “all the other records” from the Farms table, right?But it won’t — not if the Cows column contains any NULLs.Remember that not(NULL) is itself NULL.So for any row where Cows is NULL, “Cows > 0” will be NULL, and “NOT (Cows > 0)” will be NULL as well.

SELECT Farmer, Cows, Sheep FROM Farms WHERE Cows + Sheep > 0

On the surface, this looks like a query returning all the farms that have at least one cow and/or sheep (assuming that neither Cows nor Sheep can be a negative number).However, if farmer Fred has 30 cows and an unknown number of sheep, the sum Cows + Sheep becomes NULL, and the entire search expression boils down to “NULL > 0”, which is…​ you got it.So despite his 30 cows, our friend Fred won’t make it into the result set.

As a last example, we shall rewrite the previous statement so that it will return any farm which has at least one animal of a known kind, even if the other number is NULL.To do that, we exploit the fact that “NULL or true” returns true — one of the rare occasions where a NULL operand doesn’t render the entire expression NULL:

SELECT Farmer, Cows, Sheep FROM Farms WHERE Cows > 0 OR Sheep > 0

This time, Fred’s thirty cows will make the first comparison true, while the sheep bit is still NULL.So we have “true or NULL”, which is true, and the row will be included in the output set.

Caution

If your search condition contains one or more IN predicates, there is the additional complication that some of the list elements (or subselect results) may be NULL.The implications of this are discussed in [nullguide-pred-in].

Sorts

In Firebird 2, NULLs are considered “smaller” than anything else when it comes to sorting.Consequently, they come first in ascending sorts and last in descending sorts.You can override this default placement by adding a NULLS FIRST or NULLS LAST directive to the ORDER BY clause.

In earlier versions, NULLs were always placed at the end of a sorted set, no matter whether the order was ascending or descending.For Firebird 1.0, that was the end of the story: NULLs would always come last in any sorted set, period.Firebird 1.5 introduced the NULLS FIRST/LAST syntax, so you could force them to the top or bottom.

To sum it all up:

Table 1. NULLs placement in ordered columns

Ordering

NULLs placement

Firebird 1

Firebird 1.5

Firebird 2

order by Field [asc]

bottom

bottom

top

order by Field desc

bottom

bottom

bottom

order by Field [asc | desc] nulls first

 — 

top

top

order by Field [asc | desc] nulls last

 — 

bottom

bottom

Specifying NULLS FIRST on an ascending or NULLS LAST on a descending sort in Firebird 2 is of course rather pointless, but perfectly legal.The same is true for NULLS LAST on any sort in Firebird 1.5.

Note

  • If you override the default NULLs placement, no index will be used for sorting.In Firebird 1.5, that is the case with NULLS FIRST.In 2.0 and higher, with NULLS LAST on ascending and NULLS FIRST on descending sorts.

  • If you open a pre-2.0 database with Firebird 2, it will show the old NULL ordering behaviour (that is: at the bottom, unless overridden by NULLS FIRST).A backup-restore cycle will fix this, provided that at least the restore is executed with Firebird 2’s gbak!

  • Firebird 2.0 has a bug that causes the NULLS FIRST|LAST directive to fail under certain circumstances with SELECT DISTINCT.See the bugs list for more details.
Warning

Don’t be tempted into thinking that, because NULL is now the “smallest thing” in sorts, an expression like “NULL < 3” will return true in Firebird 2 and up.It won’t.Using NULL in this kind of expression will always give a NULL outcome.

Aggregate functions

The aggregate functions — COUNT, SUM, AVG, MAX, MIN and LIST — don’t handle NULL in the same way as ordinary functions and operators.Instead of returning NULL as soon as a NULL operand is encountered, they only take non-NULL fields into consideration while computing the outcome.That is, if you have this table:

MyTable

ID

Name

Amount

1

John

37

2

Jack

NULL

3

Jim

5

4

Joe

12

5

Josh

NULL

...the statement select sum(Amount) from MyTable returns 54, which is 37 + 5 + 12.Had all five fields been summed, the result would have been NULL.For AVG, the non-NULL fields are summed and the sum divided by the number of non-NULL fields.

There is one exception to this rule: COUNT(*) returns the count of all rows, even rows whose fields are all NULL.But COUNT(FieldName) behaves like the other aggregate functions in that it only counts rows where the specified field is not NULL.

Another thing worth knowing is that COUNT(*) and COUNT(FieldName) never return NULL: if there are no rows in the set, both functions return 0.COUNT(FieldName) also returns 0 if all FieldName fields in the set are NULL.The other aggregate functions return NULL in such cases.Be warned that SUM even returns NULL if used on an empty set, which is contrary to common logic (if there are no rows, the average, maximum and minimum are undefined, but the sum is known to be zero).

Now let’s put all that knowledge in a table for your easy reference:

Table 1. Aggregate function results with different column states

Function

Results

Empty set

All-NULL set or column

Other sets or columns

COUNT(*)

0

Total number of rows

Total number of rows

COUNT(Field)

0

0

Number of rows where Field is not NULL

MAX, MIN

NULL

NULL

Max or min value found in the column

SUM

NULL

NULL

Sum of non-NULL values in the column

AVG

NULL

NULL

Average of non-NULL values in the column.This equals SUM(Field) / COUNT(Field).[1]

LIST[2]

NULL

NULL

Comma-separated string concatenation of non-NULL values in the column
1. If Field is of an integer type, AVG is always rounded towards 0. For instance, 6 non-null INT records with a sum of -11 yield an average of -1, not -2.
2. LIST was added in Firebird 2.1

The GROUP BY clause

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:

SELECT Dept, SUM(Salary) FROM Employee GROUP BY Dept

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

DEPT                     SUM
=====================

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

First notice that the people whose department is unknown (`NULL`) are grouped together, although you can't say that they have the same _value_ in the Dept field.
But the alternative would have been to give each of those records a "`group`" of their own.
Not only would this possibly add a huge number of lines to the output, but it would also defeat the purpose of __group__ing: those lines wouldn't be aggregates, but simple "```SELECT Dept, Salary```" rows.
So it makes sense to group the `NULL` depts by their state and the rest by their value.

Anyway, the `Dept` field is not what interests us most.
What does the aggregate `SUM` column tell us?
That all salaries are non-`NULL`, except in department 120?
No.
All we can say is that in every department except 120, there is at least one employee with a known salary in the database.
Each department _may_ contain `NULL` salaries;
in dept. 120 _all_ the salaries are `NULL`.

You can find out more by throwing in one or more `COUNT()` columns.
For instance, if you want to know the number of `NULL` salaries in each group, add a column "```COUNT({asterisk}) – COUNT(Salary)```".

Counting frequencies

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:

SELECT A, COUNT(A) FROM TT GROUP BY A

which would give you this result:

A            COUNT
============ ============
          -1            1
           1            1
           3            2
           6            1
           8            2
      <null>            0

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:

SELECT A, COUNT(*) FROM TT GROUP BY A

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

The HAVING clause

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):

  • Rows for which the HAVING condition evaluates to NULL won’t be included in the result set.(“Only true is good enough.”)

  • “HAVING <col> IS [NOT] NULL” is a legal and often useful condition, whether <col> is aggregate or not.(But if <col> is non-aggregate, you may save the engine some work by changing HAVING to WHERE and placing the condition before the “GROUP BY” clause.This goes for any condition on non-aggregate columns.)

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

...HAVING Dept IS NOT NULL

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

...HAVING SUM(Salary) IS NOT NULL

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

Conditional statements and loops

IF statements

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.

Tip

If you use Firebird 2 or higher, you can avoid all the pitfalls discussed here, simply by using [NOT] DISTINCT instead of the ‘=’ and “<>” operators!

  • Equals (‘=’)

    if (a = b) then
  MyVariable = 'Equal';
else
  MyVariable = 'Not equal';

    If a and b are both NULL, MyVariable will yet be “Not equal” after executing this code.The reason is that the expression “a = b” yields NULL if at least one of them is NULL.With a NULL test expression, the THEN block is skipped and the ELSE block executed.

  • Not equals (‘<>’)

    if (a <> b) then
  MyVariable = 'Not equal';
else
  MyVariable = 'Equal';

    Here, MyVariable will be “Equal” if a is NULL and b isn’t, or vice versa.The explanation is analogous to that of the previous example.

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:

  • Not not equals (“not (.. <> ..)”)

    if (not (a <> b)) then
  MyVariable = 'Equal';
else
  MyVariable = 'Not equal';

    In the original version, if one operand was NULL and the other wasn’t (so they were intuitively unequal), the result was “Equal”.Here, it’s “Not equal”.The explanation: one operand is NULL, therefore “a <> b” is NULL, therefore “not(a <> b)” is NULL, therefore ELSE is executed.While this result is correct where the original had it wrong, there’s no reason to rejoice: in the refactored version, the result is also “Not equal” if both operands are NULL — something that the original version “got right”.

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.

CASE expression

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

case <expression>
  when <exp1> then <result1>
  when <exp2> then <result2>
  ...
  [else <defaultresult>]
end

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:

case
  when <condition1> then <result1>
  when <condition2> then <result2>
  ...
  [else <defaultresult>]
end

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.

WHILE loops

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

while ( Counter > 12 ) do

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

while ( not Counter > 12 ) do

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.

FOR loops

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:

for <select-statement> into <var-list> do <code-block>

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!