CTE as lookup tables

In the past, I had to write queries to convert table data into user-friendly display text. One way is to use CASE expressions. For example, suppose you have a table with a column representing a country code and you want to add the country name in the final result.

sqlite> SELECT code FROM data; +------+ | codes | +------+ | we | | in | | in | +------+

One approach is to use a CASE expression in your query like this:

sqlite > SELECT code, ...> CAS code ...> WHEN 'we' THEN 'USA' ...> WHEN 'fr' THEN 'France' ...> WHEN 'in' THEN 'India' ...> END AS country ...> FROM data; +------+---------------+ | codes | country | +------+---------------+ | we | United States | | in | France | | in | India | +------+---------------+

The downside is that it's harder to read, and if you need to do something similar elsewhere in the query, you'll have to repeat the expression.

An alternative is to use a CTE like this:

sqlite> WITH country (code, name) AS ( ...> SELECT * FROM (VALUES ...> ('us', 'United States'), ('fr', 'France'), ('in', 'India') ...> ) AS codes ...> ) ...> SELECT data.code, name FROM data LEFT JOIN country ON country.code = data.code; +------+---------------+ | codes | name | +------+---------------+ | we | United States | | in | France | | in | India | +------+---------------+

Now, the CTE country becomes a lookup table that you can reference multiple times in your queries.

This approach works with SQLite and PostgreSQL.