FirebirdSQL logo

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.

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
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 [nullguide-alter-pop-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 or use it as input parameter to a stored procedure (both are special types of assignments).

if ( YourString = 'Dunno' ) then MyField = null
update Potatoes set Amount = null where Amount < 0
insert 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.