Using case when vs. joining on a mapping

Question

I want to do a simple mapping of a column e.g. a = apple, b = banana, c = cucumber.

I could either

Select 
  consumer,
  case when letter = 'a' then 'apple'
  case when letter = 'b' then 'banana'
  case when letter = 'c' then 'cucumber' end as edible    
From letter_table

Or

Select
  consumer,
  mapping.edible
from letter_table left join mapping
  on letter_table.letter = mapping.letter

Is one method generally preferable? If so why?

My main concern would be speed. With large mappings it would be cumbersome to write out the case statement and I would typically create the mapping table and use the join. With small tables I would typically write out the case statement. Assume the columns used are indexed, and the user has read write privileges.

Answer 1

Unless you have a proven performance issue, I would always go with second option, as it allows you to add new mappings and it provides an authoritative reference point instead of relying on each query to get it right. I would actually take it a step further and drop the redundant letter in favor of a foreign key pointing to the mapping table.

Another option worth considering is using an ENUM datatype to store the full value without the extra disk space. One downside is that adding new types requires an ALTER TABLE, which can be expensive for large tables.