Presto check if NULL and return default (NVL analog)

sql presto amazon-athena

Sasha Shpota · Apr 7, 2017 · Viewed 49.5k times · Source

Is there any analog of NVL in Presto DB?

I need to check if a field is NULL and return a default value.

I solve this somehow like this:

SELECT
  CASE 
    WHEN my_field is null THEN 0 
    ELSE my_field 
  END
FROM my_table

But I'm curious if there is something that could simplify this code.

Answer

The ISO SQL function for that is COALESCE

coalesce(my_field,0)

P.S. COALESCE can be used with multiple arguments. It will return the first (from the left) non-NULL argument, or NULL if not found.

e.g.

coalesce (my_field_1,my_field_2,my_field_3,my_field_4,my_field_5)