SQLite is the CASE statement expensive?

The CASE statement is preferred syntax:

• It's ANSI (92?), so it's supported on MySQL, Oracle, SQL Server, Postgres... unlike database vendor specific IF syntax
• It supports short ciruiting - the rest of the evaluation is not performed once criteria matches

Doing the separate table and JOIN is definitely the cleaner way to write this code. What happens, for example, if you want to write another query with the same mappings? You'd have to copy the CASE statement into the new query, and copy duplication is bad. What happens if you need to add a new Active state?

Performance-wise, both the JOIN and the CASE should be fairly cheap. CASE might be slightly more performant because of short-circuiting of evaluation and the few cases, but JOIN is, in my opinion, the cleaner and more flexible and more SQL-ey solution.

CASE should be much cheaper as it should not involve any I/O, but for small tables JOINs are not that expensive either (but do test it).

The question is if you will need to maintain this CASE in several queries and will you need to establish any referential integrity on it.