Spark SQL - isnull and isnotnull Functions

Spark SQL functions isnull and isnotnull can be used to check whether a value or column is null. Both functions are available from Spark 1.0.0.

Use isnull function

The following code snippet uses isnull function to check is the value/column is null.

spark-sql> select isnull('Hello, Kontext!');
false

spark-sql> SELECT t.key, t.value, isnull(t.value) as is_null
         > FROM VALUES
         > ('a',1),
         > ('b',NULL)
         > AS t(key, value);
a       1       false
b       NULL    true

Use isnotnull function

Similarly, we can also use isnotnull function to check if a value is not null.

spark-sql> select isnotnull('Hello, Kontext!');
true

spark-sql> SELECT t.key, t.value, isnotnull(t.value) as is_null
         > FROM VALUES
         > ('a',1),
         > ('b',NULL)
         > AS t(key, value);
a       1       true
b       NULL    false

Use CASE WHEN

Of course, we can also use CASE WHEN clause to check nullability. 

spark-sql> SELECT t.key, t.value, case when t.value is null then true else false end as is_null
         > FROM VALUES
         > ('a',1),
         > ('b',NULL)
         > AS t(key, value);
a       1       false
b       NULL    true