NULL Values

From DataSelf Knowledge Base
Jump to navigation Jump to search

“The inconsistencies in the SQL standard mean that it is not possible to ascribe any intuitive logical semantics to the treatment of nulls in SQL.”

Data warehouse, SQL, and data base management experts nearly all recommend avoiding the use of NULLs.

If a column of data contain a NULL then SQL experts strongly recommend using the IS NULL or IS NOT NULL operator, and/or ISNULL function on all operations on that column.

In a database, a value of NULL indicates the absence of a value. Although sometimes this value does end up being changed to be zero, NULL does not imply or equal zero. Null means simply that the value has not been specified.

In SQL any numeric operation containing a NULL is = N. This makes sense because NULL is not defined as any kind of number. Mixing numbers and NULLs, therefore, is like mixing apples and rocks. A number divided by NULL makes less sense than division by zero.

Key Points:
  • Not only is NULL not equal to anything, it’s also not unequal to anything.
  • NULL plus, minus, times, or divided by any number is = NULL.
  • The meaning of Null in a logical operation is unknown.
  • The presence of Nulls require the use of three-value logic. Three value logic includes the two values of true and false that we are used to and a third result of unknown.

For example:

  • 1 * NULL = NULL
  • NULL + 0 = NULL
  • 1 + NULL = NULL
Misunderstanding How Nulls Work

Misunderstanding of how Null works is the cause of a great number of errors in SQL code, This problem is found in ISO standard SQL statements and in the specific SQL dialects supported by real-world database management systems. These mistakes are usually the result of confusion between Null and either 0 (zero) or an empty string (a string value with a length of zero, represented in SQL as ’’).

An improperly handled NULL value can destroy any ETL process. NULL values pose the biggest risk when they are in foreign key columns. Joining two or more tables based on a column that contains NULL values will cause data loss! Remember, in a relational database NULL is not equal to NULL. That causes joins to fail. Check for NULL values in every foreign key in the source database. When NULL values are present, you must outer join the tables (recommended article)

Nulls trip up even logic experts

SQL IS NULL / IS NOT NULL binary operator.

The IS NULL and IS NOT NULL operators evaluates to true or false.

expression IS [ NOT ] NULL

MS SQL Server ISNULL Function

The ISNULL function is not found in all variants of SQL. For instance, Oracle does not have it, MySQL works somewhat differently.


Is the expression to be checked for NULL. check_expression can be of any data type.

Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expression.

Operation of Functions in SQL vs. DS ETL

ROUND Function - SQL vs. ETL

SQL: Round(NULL, any_length) = NULL thus Round(NULL, 1) = NULL
DS ETL: Round(NULL, 1) = 0; Round(NULL) = 0

CEILING Function - SQL vs. ETL

The CEILING function returns the next highest integer thus Ceiling(99.9) = 100.

SQL: Ceiling(NULL) = NULL
DS ETL: Ceiling(NULL) = 0

Nulls & SQL Server

Comparing NULLs: The NULLs Database Option in Microsoft SQL Server databases

The ANSI_NULLS ON setting, which is a database option that determines how NULL comparisons are handled. Under ANSI_NULLS ON, two NULL values will never be equal to each other because the two separate values are unknown. With ANSI_NULLS OFF, two separate NULL values will evaluate to equal values.

Null Examples from SQL Server SELECT statements

Use IS NULL or IS NOT NULL . Do not use =, !=, <> , etc.

  • SELECT * FROM MyTable WHERE MyColumn != NULL (0 Results)
  • SELECT * FROM MyTable WHERE MyColumn <> NULL (0 Results)
  • SELECT * FROM MyTable WHERE MyColumn IS NOT NULL (568 Results)

Not only is NULL not equal to anything, it’s also not unequal to anything. This is where the language is confusing.

How to Think About NULLs

NULL is not a normal value. To make things more confusing there are two major definitions of NULL. In one definition NULL is not considered to be a value at all but rather a “placeholder” or flag meaning something like ‘unknown’ or ‘missing’. The second definition, the definition used in the SQL standard, NULL is a value but a special value whose meaning depends on how it is used. Saying anything with the words “equal” or “not equal” is a trap when discussing NULLs, because the normal concepts of equality or inequality, greater than or less than do not apply to NULLs. Instead, one can only say “is” or “is not” (without the word “equal”) when discussing NULLs.