How to use WHEN CASE queires in AWS Athena

This article is a part of my "100 data engineering tutorials in 100 days" challenge. (76/100)

In AWS Athena, we can use the WHEN CASE expressions to build “switch” conditions that convert matching values into another value. Such a WHEN CASE expression consists of four parts:

  • CASE expression that produces the value that will be matched in the expression
  • WHEN value THEN result that checks whether the expression matches the value and returns the result if both are the same
  • ELSE default_result that returns the default value in the case of no matching found using the WHEN expressions. Note that if there is no ELSE default_result, the expression returns null as the default value.
  • ‘END’ that finishes the CASE WHEN block

We can use multiple WHEN expressions to build long “switch” statements, like this:

SELECT CASE column_to_be_matched
WHEN 'value_A' THEN 'the_replacement_of_value_A'
WHEN 'value_B' THEN 'the_replacement_of_value_B'
WHEN 'value_C' THEN 'the_replacement_of_value_C'
ELSE 'default'
FROM some_table

We can also use the CASE WHEN expressions to retrieve values from multiple columns and choose the correct one using the CASE WHEN statement:

SELECT client_id, CASE client_type
WHEN 'private' THEN last_name
WHEN 'company' THEN company_name
END from some_clients

Subscribe to the newsletter and join the free email course.

Remember to share on social media!
If you like this text, please share it on Facebook/Twitter/LinkedIn/Reddit or other social media.

If you want to contact me, send me a message on LinkedIn or Twitter.

Would you like to have a call and talk? Please schedule a meeting using this link.

Bartosz Mikulski
Bartosz Mikulski * MLOps Engineer / data engineer * conference speaker * co-founder of Software Craft Poznan & Poznan Scala User Group

Subscribe to the newsletter and get access to my free email course on building trustworthy data pipelines.