Tuesday, April 29, 2008

Handling NULL in SQL Statements

It is easy to be confused by how NULLs are handled by the SQL engine. NULL can be referred to as undefined or does not exist. When evaluating an expression which includes a NULL ( ex. 5 - NULL ) cannot be evaluated and thus returns a NULL.

This is particularly frustrating when concatenating strings together. For example: concatenating first, middle and last names together to get a full name can return NULL if any of the three fields is NULL.

EXCEPTIONS

There are two exceptions to this rule. First SQL Aggregate functions ( i.e. SUM, MAX, MIN, AVG, COUNT ) ignore NULLs and return results based on the values in the specified fields. The second exception are Logical or Boolean fields. Many implementations of SQL treat an unknown in a Boolean data type as True. Advantage does not treat a NULL in a Boolean field as True.

It is important to note that an empty value is different than a NULL. DBF files do not support NULLs and thus have a default empty value. With the FoxPro support included in Advantage version 9.0 allows for NULLs in FoxPro DBF files. ADT (proprietary format) tables have always supported NULLs.

Using IFNULL

IFNULL is the easiest function to use to handle NULL fields within your SQL statement. IFNULL is equivalent to the following IIF statement

IIF (Field IS NULL, <alternate value>, Field)

The <alternate value> is the value you want to be returned when the selected field is NULL. In the case of string concatenation this could be an empty string ( '' ). The IFNULL function condenses this statement and allows for better readability. The syntax is below:

IFNULL ( Field, <alternate value> )

COALESCE

The COALESCE statement returns the first non-NULL expression result. It is equivalent to the following CASE statement:

CASE
WHEN value1 IS NOT NULL THEN value1
WHEN value2 IS NOT NULL THEN value2
WHEN value3 IS NOT NULL THEN value3
...
END

The COALESCE statement condenses this complex CASE statement into a more readable format.

COALESCE ( value1, value2, value3, ... )

This article on Wikipedia contains a lot of useful information on how SQL handles NULLs.

No comments: