Firebird Documentation Index → Firebird Null Guide |
Table of Contents
NULL
?NULL
support in Firebird SQLNULL
in operationsNULL
NULL
and equality in practiceNULL
-related bugs in FirebirdTime and again, support questions pop up on the Firebird mailing lists about
“strange things” happening with NULL
s. The concept seems
difficult to grasp – perhaps partly because of the name, which suggests a kind of
“nothing” that won't do any harm if you add it to a number or stick it to the back
of a string. In reality, performing such operations will render the entire expression
NULL
.
This guide explores the behaviour of NULL
in Firebird SQL, points out
common pitfalls and shows you how to deal safely with expressions that contain
NULL
or may resolve to NULL
.
If you only need a quick reference to refresh your memory, go to the summary at the end of the guide.
In SQL, NULL
is not a value. It is a state
indicating that an item's value is unknown or nonexistent. It is not zero or blank or an
“empty string” and it does not behave like any of these values. Few things in SQL
lead to more confusion than NULL
, and yet its workings shouldn't be hard to
understand as long as you stick to the following simple definition: NULL
means unknown.
Let me repeat that:
NULL
means UNKNOWN
Keep this line in mind as you read through the rest of the guide, and most of the
seemingly illogical results you can get with NULL
will practically explain
themselves.
A few sentences and examples in this guide were taken from the Firebird Quick Start Guide, first published by IBPhoenix, now part of the Firebird Project.
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:
If you declare a variable in a stored procedure or trigger, its value is undefined
and its state is NULL
from the moment of creation until some value is
assigned to it. The same is true for output parameters in stored procedures.
If you insert a record into a table and you only provide values for part of the
fields, the remaining fields will be initialised to NULL
except where
a default value is in effect or a value is assigned by a “before insert”
trigger.
If you add a column to a table that already has records, the fields added to the
existing records will be NULL
, except if you declare the column as
NOT NULL and specify a default value for it. Note that
both conditions must be satisfied for the fields to become anything
other than NULL
.
Firebird Documentation Index → Firebird Null Guide |