Developing By Me

Project Development and Design

NULL Conditions

NULL Conditions

NULL is a unique data element available within SQL. It reacts differently to conditional statements and may not always be apparent when the data is exported as a table. A NULL field does not contain any information. This is important to understand because a number column defaults to 0 and a character string would default to ‘’ or an empty character string. When NULL is exported to a report or to a table it may appear to be either of these however within SQL it is not. The NULL value cannot be compared with either 0 or ‘’ it will always return false. Because of this there are two special logical tests the first is ‘IS NULL’ which will return the row if the field is set to the NULL value. The second is ‘IS NOT NULL’ which will return the row if the field contains any value.

 

Operator SQL Syntax Description
IS NULL field1 IS NULL field1 must equal the special NULL value
IS NOT NULL field1 IS NOT NULL field1 does not equal the special NULL value

 

 

 

There are several functions that work with NULL values. These special functions help the DBA control the output of the field that contains NULL. This means that the result is more predictable and less guess work is required when working with the dataset. The first function is ISNULL(field, default). This function will check the specified field to see if it is NULL. In the event that it is NULL the default value will be used instead. This allows the DBA to provide a consistent result when working with the data outside of SQL. The second is the NULLIF() which is the reverse of ISNULL(). The NULLIF() command takes two expressions which can be as simple as a field and a value or fully developed logical tests comparing multiple fields and values. However, using the syntax of NULLIF(field, 0) will return NULL if the field contains a 0. This is helpful because NULL is not included in math functions. The last function that can be used to affect NULL values is COALESCE(). This function takes a list of values which can be either fields or literal values. The function starts with the first argument on the left and checks to see if it is NULL. If it is NULL then it moves along the list until it finds a non-NULL value. If it reaches the end and only NULL values are found it will return NULL but if another value is provided last such as 0 or ‘’ that will force the function to return an expected DEFAULT value which should be used with this function.

Function SQL Server MySQL Oracle PostgreSQL Function Type Return Data Type Description
COALESCE(field1, field2, … , fieldN) X X X X String TEXT Returns the first non-NULL value. If all values are NULL then NULL will be returned.
IFNULL(field, value) X NULL varies Same as ISNULL()
ISNULL(field, value) X NULL varies Returns second argument if the first IS NULL
NULLIF(field) X X NULL varies Returns NULL if the first argument matches the second IS NOT NULL
NVL(field) X NULL varies Same as ISNULL()

 

%d bloggers like this: