Null column values display as NaN

Null column values correctly display as NaN in Databricks SQL.

Written by Adam Pavlacka

Last published at: March 4th, 2022

Problem

You have a table with null values in some columns. When you query the table using a select statement in Databricks, the null values appear as null.

When you query the table using the same select statement in Databricks SQL, the null values appear as NaN.

%sql

select * from default.<table-name> where <column-name> is null

Databricks

Query in Databricks returns null

Databricks SQL

Cause

NaN is short for not a number. This is how null values are displayed in Databricks SQL.

Solution

This is not a problem. Databricks SQL is working as designed.

The representation of null values in Databricks SQL is different from the representation of null values in Databricks, but the data itself is not changed.