Check if null and return default (NVL analog)

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. 

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 …

What about PrestoDB’s support when it comes to handling nulls?  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     

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