FirebirdSQL logo

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.

Document history

The exact file history is recorded in the firebird-documentation git repository; see https://github.com/FirebirdSQL/firebird-documentation

Revision History

0.1

8 Apr 2005

PV

First edition.

0.2

15 Apr 2005

PV

Mentioned that Fb 2.0 legalises “A = NULL” comparisons.

Changed text in “Testing if something is NULL”.

Slightly altered “Dealing with NULLs” section.

1.0

24 Jan 2007

PV

This is a major update, with so much new material added that the document has grown to around 4 times its former size.In addition, much of the existing text has been reorganised and thoroughly reworked.It’s not feasible to give a summary of all the changes here.Consider this a new guide with 15–25% old material.The most important additions are:

* NULL literals* IS [NOT] DISTINCT FROM* Internal functions and directives* Predicates: IN, ANY, SOME, ALL, EXISTS, SINGULAR* Searches (WHERE)* Sorts (ORDER BY)* GROUP BY and HAVING* CASE, WHILE and FOR* Keys and indices* CHECK constraints* SELECT DISTINCT* Converting values to NULL with NULLIF* Altering populated tables* Bugs list* Alphabetical index

1.0.1

26 Jan 2007

PV

Making non-nullable columns nullable again: Provisory fix of error regarding removal of NOT NULL constraints.

1.1

13 Oct 2011

PV

What is NULL?: Added subsection NULL as a default state.

NULL support in Firebird SQL :: Assigning NULL: Added 4th example.

Predicates :: EXISTS and SINGULAR: “evolves” → “evaluates” (2x) in Note.

Altering populated tables :: Adding a non-nullable field to a populated table :: Adding a NOT NULL field: Added para + additional programlisting after first programlisting.

Altering populated tables :: Adding a non-nullable field to a populated table :: Adding a CHECKed column: Added second Note.

Altering populated tables :: Adding a non-nullable field to a populated table :: Adding a non-nullable field using domains: Renamed section to Using domains to add a non-nullable field.

Altering populated tables :: Adding a non-nullable field to a populated table :: Using domains to add a non-nullable field: Two programlistings instead of one.Text heavily edited and extended.

Altering populated tables :: Making existing columns non-nullable :: Making an existing column NOT NULL: Added Warning.

Altering populated tables :: Making non-nullable columns nullable again: Largely rewritten, greatly extended and divided into subsections.

Changing the nullability of a domain :: New top-level section.For free!

Summary: Added “Every field or variable that has been created but not initialised is in a NULL state” as second listitem.

1.2

30 Jun 2020

MR

Conversion to AsciiDoc, minor copy-editing

Removed section FSQL as linked page no longer exists and no alternative location found.

License notice

The contents of this Documentation are subject to the Public Documentation License Version 1.0 (the “License”); you may only use this Documentation if you comply with the terms of this License.Copies of the License are available at https://www.firebirdsql.org/pdfmanual/pdl.pdf (PDF) and https://www.firebirdsql.org/manual/pdl.html (HTML).

The Original Documentation is titled Firebird Null Guide.

The Initial Writer of the Original Documentation is: Paul Vinkenoog.

Copyright © 2005–2020.All Rights Reserved.Initial Writer contact: paulvink at users dot sourceforge dot net.

NULL in operations

As many of us have found out to our chagrin, NULL is contagious: use it in a numerical, string or date/time operation, and the result will invariably be NULL.With boolean operators, the outcome depends on the type of operation and the value of the other operand.

Please remember that in Firebird versions prior to 2.0 it is mostly illegal to use the constant NULL directly in operations or comparisons.Wherever you see NULL in the expressions below, read it as “a field, variable or other expression that resolves to NULL”.In Firebird 2 and above this expression may also be a NULL literal.