Check if null and return default (NVL analog)

How to Check if null

SQL has a number of functions for dealing with null values, and everyone has their favorite. The complicating factor is that not all SQL engines support all the functions, and some SQL dialects have their own versions of the functions which may be completely different (remember the “S” in SQL stands for Structured, not Standard!)

Let’s look at the functions used to substitute alternative, or default, values for NULLs. These functions are really useful if you need to see zeros instead of nulls in your results, or if you need to avoid division by zero errors.  We’ll check if null and return default (nvl analog).

Oracle has the NVL(column_x, value_y) function which will return column_x UNLESS it is a null, in which case the value_y will be returned instead. 

In Microsoft SQL Server, the function is ISNULL(column, value)

In MySQL, you use IFNULL(column, value)

In most databases (MySQL included), you can use COALESCE(value1, value2, …) which returns the first non-null value in the list.

And in some databases, you will find the function NULLIF(value1, value2) which returns null if value1 equals value2, otherwise returns value1.

And in most databases, you can use good old CASE, for example:

SELECT a
       CASE
           WHEN a = 999 THEN 'this is too much'
           WHEN a is null THEN 'this is null'
           ELSE 'we are good'
       END
FROM …

Check if null | PrestoDB

What about PrestoDB’s support when it comes to handling nulls? How does Presto handle this? PrestoDB  supports the following:

FunctionDescription
COALESCE(value1, value2, …)returns the first non-null value (or column) in the list.
NULLIF(value1, value2) returns null if value1 equals value2, otherwise returns value1.
CASE..WHEN..END various forms supported – see https://prestodb.io/docs/current/functions/conditional.html for more information. Use with IS NULL to match null values. 
IF(condition, true_value, false_value)Evaluates and returns true_value if condition is true, otherwise false_value. Use with IS NULL to match null values. 

You’ll notice NVL() is not supported by PrestoDB. Use COALESCE(column_x, value_y) instead as a direct replacement. Or use IF(), or CASE statement. 

Some examples using PrestoDB:

--Check we have a null in our data
presto:default> select partkey, brand, container from parts_null_test where partkey=200001;
 partkey |   brand    | container 
---------+------------+-----------
  200001 | SuperBrand | NULL      


--Turn nulls into the string ‘Single’ using coalesce
presto:default> select partkey, brand, COALESCE(container,'SINGLE') as CONTAINER from parts_null_test where partkey=200001;
 partkey |   brand    | CONTAINER 
---------+------------+-----------
  200001 | SuperBrand | SINGLE    

--Use the IF() method to turn nulls into the string ‘Single’
presto:default> select partkey, brand, IF(container is null,'SINGLE', container) as CONTAINER from parts_null_test LIMIT 5;
 partkey |   brand    | CONTAINER  
---------+------------+------------
  200001 | SuperBrand | SINGLE     
  100001 | Brand#35   | JUMBO CASE 
  100002 | Brand#34   | WRAP CAN   
  100003 | Brand#21   | SM BOX     
  100004 | Brand#42   | SM CASE    
 
--Use a CASE statement
presto:default> select partkey, brand, CASE WHEN container is null THEN 'SINGLE' ELSE container END as CONTAINER from parts_null_test LIMIT 5;
 partkey |   brand    | CONTAINER  
---------+------------+------------
    7024 | Brand#52   | JUMBO PACK 
    7025 | Brand#15   | LG PACK    
    7026 | Brand#55   | LG BOX     
    7023 | Brand#22   | LG DRUM    
  200001 | SuperBrand | SINGLE
check if null

The above information should help you to check if null and return default (NVL analog) using the Presto if, coalesce, and case statements ( COALESCE(), IF() or CASE) in your PrestoDB SQL queries.

What is an Open Data Lake in the Cloud?

The Open Data Lake in the cloud is the solution to the massive data problem. Many companies are adopting that architecture because of better price-performance, scale, and non-proprietary architecture.

Data Warehouse Concepts for Beginners

A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. Check out this article for more information about data warehouses.