SQL Users: Reduce Your CASE Statements With A Lookup Table

Map fields in your SQL tables without using CASE.

Zach Quinn

— 6 min read
Share
White search bar on beige background.
Search bar. Photo courtesy of Valeria Nikitina on Unsplash.

You’re Using The Wrong Logic In Your SQL CASE Statements

CASE statements, while useful, are one of the most overused SQL commands.

At a certain point, if your script is dominated with CASE statements that means you’re hard coding instead of finding a dynamic way to generate your output, like using a lookup table (a concept we’ll discuss below).

For smaller, simpler use cases, like when you only need to change 1–3 fields, this is generally ok.

However, what if I wanted you to convert a numeric variable (01, 02, 03, etc.) into its respective month in string form, like the below snippet shows?

SELECT day, month 
CASE  
  WHEN month LIKE "%01%" THEN "January" 
  WHEN month LIKE "%02%" THEN "Februrary" 
  WHEN month LIKE "%03%" THEN "March" 
  WHEN month LIKE "%04%" THEN "April" 
  WHEN month LIKE "%05%" THEN "May" 
  WHEN month LIKE "%06%" THEN "June" 
  WHEN month LIKE "%07%" THEN "July" 
  WHEN month LIKE "%08%" THEN "August" 
  WHEN month LIKE "%09%" THEN "September" 
  WHEN month LIKE "%10%" THEN "October" 
  WHEN month LIKE "%11%" THEN "November" 
  WHEN month LIKE "%12%" THEN "December" 
ELSE month END AS month_str 
FROM table

This is objectively messy code that demonstrates the writer had to create a workaround for a problem that should have a dynamic solution.

Below, we’ll see how creating a lookup table reduces script messiness and increases the dynamic aspect of your SQL code.

Build Your Pipeline To A Data Engineering Career

You’ve reached the limit of the public preview. The full version of this post includes the implementation details: The code, the edge cases, and the "why" behind the architecture.

When you join PipelineToDE, you get:

  • The DA → DE Pathway Course: A structured roadmap to bridge the gap between analysis and engineering.
  • Weekly Senior Deep Dives: Fresh, tactical insights on Python, Cloud (GCP/AWS), and modern orchestration delivered every week.
  • Production-Ready Blueprints: Access to 80+ protected stories and code repos from my time in the trenches as a Senior DE
  • The DE Job Board (Coming Soon): Exclusive access to a curated board of high-agency Data Engineering roles.

Read more