Amazon Athena - ANSI SQL - Providing default value if NULL

Amazon Athena - ANSI SQL - Providing default value if NULL 

When retrieving data from a table, it can useful to provide default values instead of NULL, within the same SELECT statement.

With other database platforms, this can be done in-line. 

However with Athena's flavour of SQL, an alternative is to use a CASE..WHEN expression.

Example: here, for all rows that have a NULL value for termination_reason, then a substitute value 'Unknown' will be used.

note: to avoid ambiguity, the CASE..WHEN expression produces a new column with suffix _default. This is to avoid mixing it up with the original column.

SELECT

year_and_month,

duration,

-- Handle NULLs from older data:

case

when termination_reason is null

then 'Unknown'

else termination_reason

end as termination_reason_with_default

from my_database.my_table