Home

# How to use a CTE instead of complicated CASE expressions

In the past I’ve had to write queries to convert data in a table into user-friendly display text. One way to do this is with CASE expressions. For example, let’s say you have a table with a column being a country code, and you want to add the country name in the final result.

1
2
3
4
5
6
7
8
sqlite> SELECT code FROM data;
+------+
| code |
+------+
| us   |
| fr   |
| in   |
+------+

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
sqlite> SELECT code,
   ...> CASE code 
   ...>   WHEN 'us' THEN 'United States'
   ...>   WHEN 'fr' THEN 'France'
   ...>   WHEN 'in' THEN 'India'
   ...>  END AS country
   ...> FROM data;
+------+---------------+
| code |    country    |
+------+---------------+
| us   | United States |
| fr   | 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:

1
2
3
4
5
6
7
8
9
10
11
12
13
sqlite> WITH countries (code, name) AS (
   ...>   SELECT * FROM (VALUES
   ...>     ('us', 'United States'), ('fr', 'France'), ('in', 'India')
   ...>   ) AS codes
   ...> )
   ...> SELECT data.code, name FROM data LEFT JOIN countries ON countries.code = data.code;
+------+---------------+
| code |     name      |
+------+---------------+
| us   | United States |
| fr   | France        |
| in   | India         |
+------+---------------+

Now the countries CTE becomes a lookup table that you can reference several times in your queries.

This approach works with SQLite and PostgreSQL.

Comments

In Oracle can use as below.

1
2
3
4
5
6
WITH countries(code, name) AS (
  SELECT 'us', 'United States' FROM dual UNION ALL
  SELECT 'fr', 'France' FROM dual UNION ALL
  SELECT 'in', 'India' FROM dual
)
SELECT data.code, name FROM data LEFT JOIN countries ON countries.code = data.code;
Edited on