Because NULL means “value unknown”, it is the logical default state for any field or variable that has been created but not provided with a value:

NULLs are never allowed in primary keys.A column can only be (part of) a PK if it has been defined as NOT NULL, either in the column definition or in a domain definition.Note that a “CHECK (XXX IS NOT NULL)” constraint won’t do: you need a NOT NULL specifier right after the data type.

Foreign keys as such impose no restrictions with respect to NULLs.Foreign key columns must always reference a column (or set of columns) that is a primary key or a unique key.A unique index on the referenced column(s) is not enough.

Even if NULLs are absolutely forbidden in the target key (for instance if the target is a PK), the foreign key column may still contain NULLs, unless this is prevented by additional constraints.

Teaching you how to declare, use, and write UDFs is outside the scope of this guide.However, we must warn you that UDFs can occasionally perform unexpected NULL conversions.This will sometimes result in NULL input being converted to a regular value, and other times in the nullification of valid input like '' (an empty string).

The main cause of this problem is that with “old style” UDF calling (inherited from InterBase), it is not possible to pass NULL as input to the function.When a UDF like LTRIM (left trim) is called with a NULL argument, the argument is passed to the function as an empty string.(Note: in Firebird 2 and up, it can also be passed as a null pointer.We’ll get to that later.)From inside the function there is no way of telling if this argument represents a real empty string or a NULL.So what does the function implementor do?He has to make a choice: either take the argument at face value, or assume it was originally a NULL and treat it accordingly.

If the function result type is a pointer, returning NULL is possible even if receiving NULL isn’t.Thus, the following unexpected things can happen:

Both conversions are usually unwanted, but the second probably more so than the first (better validate something NULL than wreck something valid).To get back to our LTRIM example: in Firebird 1.0, this function returns NULL if you feed it an empty string.This is wrong.In 1.5 it never returns NULL: even NULL strings (passed by the engine as '') are “trimmed” to empty strings.This is also wrong, but it’s considered the lesser of two evils.Firebird 2 has finally got it right: a NULL string gives a NULL result, an empty string is trimmed to an empty string — at least if you declare the function in the right way.

As early as in Firebird 1.0, a new method of passing UDF arguments and results was introduced: “by descriptor”.Descriptors allow NULL signalling no matter the type of data.The fbudf library makes ample use of this technique.Unfortunately, using descriptors is rather cumbersome;it’s more work and less play for the UDF implementor.But they do solve all the traditional NULL problems, and for the caller they’re just as easy to use as old-style UDFs.

Firebird 2 comes with a somewhat improved calling mechanism for old-style UDFs.The engine will now pass NULL input as a null pointer to the function, if the function has been declared to the database with a NULL keyword after the argument(s) in question, e.g. like this:

This requirement ensures that existing databases and their applications can continue to function like before.Leave out the NULL keyword and the function will behave like it did under Firebird 1.5 and earlier.

Please note that you can’t just add NULL keywords to your declarations and then expect every function to handle NULL input correctly.Each function has to be (re)written in such a way that NULLs are dealt with correctly.Always look at the declarations provided by the function implementor.For the functions in the ib_udf library, consult ib_udf2.sql in the Firebird UDF directory.Notice the 2 in the file name;the old-style declarations are in ib_udf.sql.

These are the ib_udf functions that have been updated to recognise NULL input and handle it properly:

Most ib_udf functions remain as they were;in any case, passing NULL to an old-style UDF is never possible if the argument isn’t of a referenced type.

On a side note: don’t use lower, *trim and substr* in new code;use the internal functions LOWER, TRIM and SUBSTRING instead.

The unsolicited NULL <=> non-NULL conversions described earlier usually only happen with legacy UDFs, but there are a lot of them around (most notably in ib_udf).Also, nothing will stop a careless implementor from doing the same in a descriptor-style function.So the bottom line is: if you use a UDF and you don’t know how it behaves with respect to NULL:

The declarations for the shipped UDF libraries can be found in the Firebird subdirectory examples (v.1.0) or UDF (v.1.5 and up).Look at the files with extension .sql

To learn more about UDFs, consult the InterBase 6.0 Developer’s Guide (free at https://www.ibphoenix.com/downloads/60DevGuide.zip), Using Firebird and the Firebird Reference Guide (both on CD), or the Firebird Book.CD and book can be purchased via https://www.ibphoenix.com.

The COALESCE function in Firebird 1.5 and higher can convert NULL to most anything else.This enables you to perform an on-the-fly conversion and use the result in your further processing, without the need for “if (MyExpression is null) then” or similar constructions.The function signature is:

COALESCE returns the value of the first non-NULL expression in the argument list.If all the expressions are NULL, it returns NULL.

This is how you would use COALESCE to construct a person’s full name from the first, middle and last names, assuming that some middle name fields may be NULL:

Or, to create an as-informal-as-possible name from a table that also includes nicknames, and assuming that both nickname and first name may be NULL:

COALESCE will only help you out in situations where NULL can be treated in the same way as some allowed value for the datatype.If NULL needs special handling, different from any other value, your only option is to use an IF or CASE construct after all.

Firebird 1.0 doesn’t have COALESCE.However, you can use four UDFs that provide a good part of its functionality.These UDFs reside in the fbudf lib and they are:

The *NVL functions take exactly two arguments.Like COALESCE, they return the first argument if it’s not NULL;otherwise, they return the second.Please note that the Firebird 1.0 fbudf lib — and therefore, the *NVL function set — is only available for Windows.

Sometimes you want certain values to show up as NULL in the output (or intermediate output).This doesn’t happen often, but it may for instance be useful if you want to exclude certain values from summing or averaging.The NULLIF functions can do this for you, though only for one value at the time.

The NULLIF internal function takes two arguments.If their values are equal, the function returns NULL.Otherwise, it returns the value of the first argument.

A typical use is e.g.

which will give you the average weight of the FatPeople population, without counting those with weight -1.(Remember that aggregate functions like AVG exclude all NULL fields from the computation.)

Elaborating on this example, suppose that until now you have used the value -1 to indicate “weight unknown” because you weren’t comfortable with NULLs.After reading this guide, you may feel brave enough to give the command:

Now unknown weights will really be unknown.

Firebird 1.0.x doesn’t have the NULLIF internal function.Instead, it has four user-defined functions in the fbudf lib that serve the same purpose:

Please note that the Firebird 1.0 fbudf lib — and therefore, the *nullif function set — is only available for Windows.

Suppose you have this table:

You have already entered some adventure games in this table when you decide to add a non-nullable ID field.There are two ways to go about this, both with their own specific problems.

This is by far the preferred method in general, but it causes some special problems if used on a populated table, as you will see in a moment.First, add the field with the following statement:

Or, if you want to name the constraint explicitly (this makes it easier if you ever want to drop it later):

Despite the NOT NULL constraint, the new ID fields that have been added to the existing rows will all be NULL.In this special case, Firebird allows invalid data to be present in a column.It will even write the NULLs to a backup without complaining, but it will refuse to restore them, precisely because of the constraint violation.

To make matters worse, Firebird lies to you when you retrieve data from the table.With isql and many other clients, “SELECT * FROM ADVENTURES” will return this dataset:

Of course this will make most people think “OK, cool: Firebird used a default value of 0 for the new fields — nothing to worry about”.But you can verify that the ID fields are really NULL with these queries:

Another type of query hinting that something fishy is going on is the following:

Such a query will return 0 in the “ID+3” column.With a true 0 ID it should have been 3.The correct result would be NULL, of course!

If the added NOT NULL column is of type (VAR)CHAR instead of INT, you will see phoney emptystrings ('').With a DATE column, phoney “zero dates” of 17 November 1858 (epoch of the Modified Julian Day).In all cases, the true state of the data is NULL.

...is going on here?

When a client application like isql queries the server, the conversation passes through several stages.During one of them — the “describe” phase — the engine reports type and nullability for each column that will appear in the result set.It does this in a data structure which is later also used to retrieve the actual row data.For columns flagged as NOT NULL by the server, there is no way to return NULLs to the client — unless the client flips back the flag before entering the data retrieval stage.Most client applications don’t do this.After all, if the server assures you that a column can’t contain NULLs, why would you think you know better, override the server’s decision and check for NULLs anyway?And yet that’s exactly what you should do if you want to avoid the risk of reporting false values to your users.

Here’s what you should do to make sure that your data are valid when adding a NOT NULL column to a populated table:

Using a CHECK constraint is another way to disallow NULL entries in a column:

If you do it this way, a subsequent SELECT will return:

Well, at least now you can see that the fields are NULL!Firebird does not enforce CHECK constraints on existing rows when you add new fields.The same is true if you add checks to existing fields with ADD CONSTRAINT or ADD CHECK.

This time, Firebird not only tolerates the presence and the backing up of the NULL entries, but it will also restore them.Firebird’s gbak tool does restore CHECK constraints, but doesn’t apply them to the existing data in the backup.

This restorability of your NULL data despite the presence of the CHECK constraint is consistent with the fact that Firebird allows them to be present in the first place, and to be backed up as well.But from a pragmatical point of view, there’s a downside: you can now go through cycle after cycle of backup and restore, and your “illegal” data will survive without you even receiving a warning.So again: make sure that your existing rows obey the new rule immediately after adding the constrained column.The “default” trick won’t work here;you’ll just have to remember to set the right value(s) yourself.If you forget it now, chances are that your outlawed NULLs will survive for a long time, as there won’t be any wake-up calls later on.

Instead of specifying data types and constraints directly, you can also use domains, e.g. like this:

Or like this:

For the presence of NULLs in the added columns, returning of false 0's, effects of default values etc., it makes no difference at all whether you take the domain route or the direct approach.The only difference is that domain-based constraints can’t be removed at the column level.So if you ever want to drop the constraint later, you must either switch the column to another domain or built-in type again, or remove the constraint from the entire domain.The latter operation is described in the section [nullguide-change-domain-nullability].

You cannot add NOT NULL to an existing column, but there’s a simple workaround.Suppose the current type is int, then this:

will change the column type to “int not null”.

If the table already had records, any NULLs in the column will remain NULL, and again most Firebird clients will report them as 0 to the user.The situation is almost exactly the same as when you add a NOT NULL column (see [nullguide-add-not-null-field]).The only difference is that if you give the domain (and therefore the column) a default value, this time you can’t be sure that it will be applied to the existing NULL entries.Tests show that sometimes the default is applied to all NULLs, sometimes to none, and in a few cases to some of the existing entries but not to others!Bottom line: if you change a column’s type and the new type includes a default, double-check the existing entries — especially if they “seem to be” 0 or zero-equivalents.

To add a CHECK constraint to a column, use one of the following syntaxes:

The second form is preferred because it gives you an easy handle to drop the check, but the constraints themselves function exactly the same.As you may have expected, existing NULLs in the column will remain, can be backed up and restored, etc.etc. — see [nullguide-add-check-not-null-field].

If you gave the NOT NULL constraint a name when you created it, you can simply drop it:

If you forgot the name, you can retrieve it with isql’s `SHOW TABLE command (i.c. SHOW TABLE ADVENTURES);other clients may have their own provisions to let you find or browse constraint names.

If you didn’t name the constraint explicitly, Firebird has created a name for it, but SHOW TABLE won’t display it.You have to use this piece of SQL to dig it up:

Don’t break your head over some of the table and field names in this statement;they are illogical but correct.Make sure to uppercase the names of your table and field if they were defined case-insensitively.Otherwise, match the case exactly but don’t enclose the names in double-quotes like you would do in a regular query.Also don’t include the angle brackets (<>). Once you have the constraint name, you can drop it just like in the previous example.

If the NOT NULL constraint came with a domain, it is not registered directly with the column.This means you can’t DROP it from the column either.Instead, change the column’s type to a nullable domain or built-in data type:

Even though this time the constraint was not tied directly to the column, you must again close all connections and reconnect before NULL input is accepted.

If you used a CHECK constraint to make the column non-nullable, you can simply drop it again:

If you haven’t named the constraint yourself but added the CHECK directly to the column or table, you must first find out its name before you can drop it.This can be done with the isql “SHOW TABLE” command (in this case: SHOW TABLE STK).Unlike NOT NULL constraints, CHECKs will also be shown if they were created without a user-defined name.

You can’t disable a domain-based CHECK constraint on the column level (you can add an extra CHECK, but the one from the domain will stay in effect as well).So unless you want to remove the CHECK from the entire domain, you’ll have to change the column’s data type to a domain or built-in type that allows NULLs, e.g.:

If the domain doesn’t have a CHECK constraint yet, you can add one like this:

You may leave out the constraint keyword if you wish.The added CHECK takes effect immediately for all the columns that are based on the domain.However, the check is only applied to new updates and inserts;existing NULL data will remain in place and will continue to be shown as <null> in result sets.

A domain can have at most one CHECK constraint.There is no ALTER CHECK statement;if you want to change the CHECK, you must drop it and create a new one.

This is how you drop a CHECK constraint from a domain:

You must close all connections and reconnect before you can insert values that would have violated the dropped CHECK in MyDomain-based columns.

Once a domain is created, Firebird doesn’t allow you to add or remove a NOT NULL constraint (DROP CONSTRAINT will only drop a CHECK).If you want to change a domain-wide NOT NULL setting, the official procedure is:

This is fine when it only concerns a few columns, but what if there are dozens or even hundreds?It is possible to change the setting by going directly to the system table.Be aware however that Firebird does not recommend or support this type of operation, nor is it guaranteed to keep working in future versions.That being said, it’s a relatively simple operation and if properly executed it shouldn’t cause you any problems.

So here’s the SQL, but remember: at your own risk!

To make a domain NOT NULL, <value> should be 1.To remove a NOT NULL constraint, use 0 or NULL.

Write the domain name in all-caps if it was created case-insensitively;otherwise, match the case exactly.Don’t use double-quotes and don’t include the ‘<>’.Also note that, even when DDL autocommit is on (which is the default in isql and many other clients), this statement won’t be autocommitted because technically it’s not DDL.So don’t forget to commit!

If you have set the flag to 1, a subsequent SHOW DOMAIN will immediately report the domain as being NOT NULL.Likewise, SHOW TABLE will list all columns based on the domain as NOT NULL.But if those columns already contained NULLs, a SELECT still truthfully reports them as such (the result set column is still “described” as nullable) — for now.You won’t be able to get any new NULLs in the column though.Close all connections and reconnect, and any illegal NULLs will appear as zeroes (at least in most clients;if this surprises you, read [nullguide-nulls-reported-as-zeroes], earlier in this guide).

If you have changed the flag from 1 to 0 or NULL — making the domain nullable — SHOW DOMAIN and SHOW TABLE will immediately report the domain and “its” columns as nullable.But you still can’t insert NULLs in the columns, and any present NULLs are still shown as zeroes (in most clients).Close all connections and reconnect to straighten everything out.

Lastly, please be warned again that this type of fiddling with the system tables is not recommended or supported by Firebird, and not guaranteed to keep working in future versions.If the number of columns based on the domain is limited, it’s better to switch them over to another domain or built-in type and then drop the old domain.

Quite often, you don’t need to take special measures for fields or variables that may be NULL.For instance, if you do this:

you probably don’t want to see the customers whose town is unspecified.Likewise:

doesn’t include people of unknown age, which is also defensible.But:

seems less justified: if you don’t know a person’s age, you shouldn’t explicitly deny her the right to vote.Worse, this:

won’t have the same effect as the previous.If some of the NULL ages are in reality under 18, you’re now letting minors vote!

The right approach here is to test for NULL explicitly:

Since this code handles more than two possibilities, using the CASE syntax (supported by Firebird 1.5 and up) is more elegant:

Or, prettier:

Sometimes you want to find out if two fields or variables are the same and you want to consider them equal if they are both NULL.The way to do this depends on your Firebird version.

In Firebird 2 and higher, you test for null-encompassing equality with DISTINCT.This has already been discussed, but here’s a quick recap.Two expressions are considered:

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

Skip the next section if you’re not interested in the pre-Firebird-2 stuff.

Pre-2.0 versions of Firebird don’t support this use of DISTINCT.Consequently, the tests are a little more complicated and there are some pitfalls to avoid.

The correct equality test for pre-2.0 Firebird versions is:

or, if you want to make the precedence of the operations explicit:

A word of warning though: if exactly one of A and B is NULL, the test expression becomes NULL, not false!This is OK in an if statement, and we can even add an else clause which will be executed if A and B are not equal (including when one is NULL and the other isn’t):

But don’t get the bright idea of inverting the expression and using it as an inequality test:

The above code will work correctly if A and B are both NULL or both non-NULL.But it will fail to execute the then clause if exactly one of them is NULL.

If you only want something to be done if A and B are different, either use one of the correct expressions shown above and put a dummy statement in the then clause (starting at 1.5, an empty begin …​ end block is also allowed), or use this longer test expression:

Remember, all this is only necessary in pre-2.0 Firebird versions.From version 2 onward, the inequality test is simply “if (A is distinct from B)”.

Please keep in mind that with Firebird 1.5.x and earlier:

In an IF or WHERE context, these NULL results act as false — which is fine for our purposes.But remember that an inversion with NOT() will also return NULL — not “true”.Also, if you use the 1.5-and-earlier tests within CHECK constraints in Firebird 2 or higher, be sure to read the section [nullguide-check-constraints], if you haven’t done so already.

In triggers you often want to test if a certain field has changed (including: gone from NULL to non-NULL or vice versa) or stayed the same (including: kept its NULL state).This is nothing but a special case of testing the (in)equality of two fields, so here again our approach depends on the Firebird version.

In Firebird 2 and higher we use this code:

And in older versions:

EXECUTE STATEMENT with a NULL argument crashed Firebird 1.5 and 1.5.1 servers.Fixed in 1.5.2.

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

FIRST and SKIP crash a Firebird 1.5.n or lower server if given a NULL argument.Fixed in 2.0.

Using LIKE with a NULL escape character would crash the server.Fixed in 1.5.1.

NULLs can exist in NOT NULL columns in the following situations:

Firebird allows these NULLs to stay, also backs them up, but refuses to restore them with gbak.See [nullguide-add-not-null-field] and [nullguide-make-column-not-null].

This is the complement of the previous bug.LPAD for instance returns NULL if you want to pad an empty string with 10 dots.This function and others are fixed in 2.0, with the annotation that you must explicitly declare them with the NULL keyword or they’ll show the old — buggy — behaviour.LTRIM and RTRIM trim empty strings to NULL in Firebird 1.0.n.This is fixed in 1.5 at the expense of returning '' when trimming a NULL string, and only fully fixed in 2.0 (if declared with the NULL keyword).

NOT SINGULAR sometimes returns NULL where SINGULAR returns true or false.Fixed in 2.0.

SINGULAR may wrongly return NULL, in an inconsistent but reproducible manner.Fixed in 2.1.

See the section on [nullguide-pred-singular].

If a NOT NULL column contains NULLs (see previous bug), the server will still describe it as non-nullable to the client.Since most clients don’t question this assurance from the server, they will present these NULLs as 0 (or equivalent) to the user.See [nullguide-nulls-reported-as-zeroes].

The following bug appeared in Firebird 1.5: if you had a table with some rows and you added a NOT NULL column (which automatically creates NULL entries in the existing rows — see above), you could make that column the primary key even though it had NULL entries.In 1.0 this didn’t work because of the stricter rules for UNIQUE indices.Fixed in 2.0.

The engine describes SUBSTRING result columns as non-nullable in the following two cases:

This is incorrect because even with a known string, substrings may be NULL, namely if the one of the other arguments is NULL.In versions 1.* this bug didn’t bite: the FROM and FOR args had to be literal values, so they could never be NULL.But as from Firebird 2, any expression that resolves to the required data type is allowed.And although the engine correctly returns NULL whenever any argument is NULL, it describes the result column as non-nullable, so most clients show the result as an empty string.

This bug seems to be fixed in 2.1.

Gbak -n[o_validity] restored NOT NULL constraints in early Firebird versions.Fixed in 1.5.1.

Let A be the expression on the left-hand side and S the result set of the subselect.In versions prior to 2.0, “IN”, “= ANY” and “= SOME” return false instead of NULL if an index is active on the subselect column and:

See the warnings in the IN and ANY sections.Workaround: use “<> ALL” instead.Fixed in 2.0.

With every operator except ‘<>’, ALL may return wrong results if an index is active on the subselect column.This can happen with our without NULLs involved.See the ALL bug warning.Fixed in 2.0.

Firebird 2.0 has the following bug: if a SELECT DISTINCT is combined with an [ASC] NULLS LAST or DESC NULLS FIRST ordering, and the ordering field(s) form(s) the beginning (but not the whole) of the select list, every field in the ORDER BY clause that is followed by a field with a different (or no) ordering gets the NULLs placed at the default relative location, ignoring the NULLS XXX directive.Fixed in 2.0.1 and 2.1.

This should definitely be considered a bug.If an angle is unknown, don’t tell me that its cosine is 1!Although the history of these functions is known and we can understand why they behave like they do (see [nullguide-udfs]), it’s still wrong.Incorrect results are returned and this should not happen.Most of the math functions in ib_udf, as well as some others, have this bug.

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.