NULL support in Firebird SQL

`NULL` support in Firebird SQL

Disallowing NULL
Testing for NULL
Assigning NULL
Testing DISTINCTness (Firebird 2+)
The NULL literal

Only a few language elements are purposely designed to give an unambiguous result with NULL (unambiguous in the sense that some specific action is taken and/or a non-NULL result is returned). They are discussed in the following paragraphs.

Disallowing `NULL`

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

create table MyTable ( i int not null )

create domain DTown as varchar( 32 ) not null

Special care should be taken when adding a NOT NULL field to an existing table that already contains records. This operation will be discussed in detail in the section Altering populated tables.

Testing for `NULL`

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

<expression> IS [NOT] NULL

Examples:

if ( MyField is null ) then YourString = 'Dunno'

select * from Pupils where PhoneNumber is not null

select * from Pupils where not ( PhoneNumber is null )
/* does the same as the previous example */

update Numbers set Total = A + B + C where A + B + C is not null

delete from Phonebook where PhoneNum is null

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.

Assigning `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:

if ( YourString = 'Dunno' ) then MyField = null

update Potatoes set Amount = null where Amount < 0

insert into MyTable values ( 3, '8-May-2004', NULL, 'What?' )

Remember:

You cannot – and should not – use the comparison operator “=” to test if something is NULL...
...but you can – and often must – use the assignment operator “=” to set something to NULL.

Testing DISTINCTness (Firebird 2+)

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

if ( A is distinct from B ) then...

if ( Buyer1 is not distinct from Buyer2 ) then...

Fields, variables and other expressions are considered:

DISTINCT if they have different values or if one of them is NULL and the other isn't;
NOT DISTINCT if they have the same value or if both of them are NULL.

[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 `NULL` literal

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

Firebird 1.5 and below

In Firebird 1.5 and below you can only use the literal word “NULL” in a few situations, namely the ones described in the previous paragraphs plus a few others such as “cast( NULL as <datatype> )” and “select NULL from MyTable”.

In all other circumstances, Firebird will complain that NULL is an unknown token. If you really must use NULL in such a context, you have to resort to tricks like “cast( NULL as int )”, or using a field or variable that you know is NULL, etc.

Firebird 2.0 and up

Firebird 2 allows the use of NULL literals in every context where a normal value can also be entered. You can e.g. include NULL in an IN() list, write expressions like “if ( MyField = NULL ) then...”, and so on. However, as a general rule you should not make use of these new possibilities! In almost every thinkable situation, such use of NULL literals is a sign of poor SQL design and will lead to NULL results where you meant to get true or false. In that sense the earlier, more restrictive policy was safer, although you could always bypass it with casts etc. – but at least you had to take deliberate steps to bypass it.