SQLite is the CASE statement expensive?

Question

I'm wondering if using a CASE statement in SQLite (or other SQL engines) to replace data is not advised. For example lets say I have a query.

SELECT Users, 
                CASE WHEN Active = 0 THEN 'Inactive'
                        WHEN Active = 1 THEN 'Active'
                        WHEN Active = 2 THEN 'Processing'
                        ELSE 'ERROR' AS Active
FROM UsersTable;

When is it better to create a reference table and perform a JOIN. In this case I would create a Table 'ActiveStatesTable' with ActiveID, ActiveDescription and perform the JOIN.

Answer 1

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

Answer 2

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.

Answer 3

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.