Firebird Documentation Index → Firebird Null Guide → NULL support in Firebird SQL |
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.
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
alter table Sales add TransTime timestamp not null
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 Altering populated tables.
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
.
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).
if ( YourString = 'Dunno' ) then MyField = nullupdate Potatoes set Amount = null where Amount < 0insert into MyTable values ( 3, '8-May-2004', NULL, 'What?' )select * from ProcessThis(88, 'Apes', Null)
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
.
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 ability to use NULL
literals depends on your Firebird
version.
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 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.
Firebird Documentation Index → Firebird Null Guide → NULL support in Firebird SQL |