Firebird DocsetFirebird Database Docs → Firebird Null Guide
Firebird home Firebird home Home: Firebird DocsetUp: Firebird Database DocsNext: NULL support in Firebird SQL

Firebird Null Guide

NULL behaviour and pitfalls in Firebird SQL

Paul Vinkenoog

26 January 2007 – Document version 1.0.1

Table of Contents

What is NULL?
NULL support in Firebird SQL
NULL in operations
Internal functions and directives
Aggregate functions
Conditional statements and loops
Keys and unique indices
CHECK constraints
User-Defined Functions (UDFs)
Converting to and from NULL
Altering populated tables
Testing for NULL and equality in practice
A. NULL-related bugs in Firebird
Bugs that crash the server
Other bugs
B. Document history
C. License notice
Alphabetical index

What is NULL?

Time and again, support questions pop up on the Firebird mailing lists about “strange things” happening with NULLs. 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.

So – what is it?

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:


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.

Home: Firebird DocsetUp: Firebird Database DocsNext: NULL support in Firebird SQL
Firebird DocsetFirebird Database Docs → Firebird Null Guide