community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

How to Handle Null Values in Databases

Moderator
Moderator
Created on

Handling Null Values in Databases

In relational databases a null value is used to represent a missing or unknown value in the column. The ANSI SQL-92 specification defines the handling of the null value in databases. 

 

 

SQL Ternary Logic

SQL relies on a three valued logic. Additionally to true and false logical expressions can also yield unknown. 

This is a consequence of SQL's support of NULL to mark either absent or unknown data. Thus, if NULL is part of a logical expression the outcome will be NULLIn more colloquial terms NULL means "it could have any value".

 

Therefore, in all the below cases the result will be unknown:

 

NULL = NULL

NULL = 1

NULL <> 1

NULL > 1

 

Please note that the SQL standard does not specify how NULL values should be ordered in comparison with non-NULL values.

 

Testing for NULL

From the above it emerges that a specific SQL command is needed to check whether a value is indeed NULL.

SQL has the <expression> is null and <expression> is not null expressions to ascertain an expression is NULL or not.

 

The SQL standard also defines an expression to compare two NULL values treating them the same. However, not all SQL flavors support it and may instead have their own proprietary alternative. The SQL standard specifies <expression> is not distinct from <expression>.

 

Where and Having Clauses

A Where clause needs to be true for the SQL query to execute. 

Therefore if the condition in the Where clause will evaluate to unknown it will reject all rows.

For instance

 

SELECT c_columns

FROM t_table

WHERE c_columns = NULL

 

will reject all columns. In order to use NULL in Where clauses WHERE c_columns is NULL should be used instead.

 

It is important to note that since NULL is neither true nor false, but rather unknown in SQL logic the below query will never return NULL values.

 

SELECT c_columns
FROM t_table
WHERE c_columns = 'ANY VALUE'

 

Additional Resource