CTEs as lookup tables

Occasionally, SQL surprises with bits of composability, such as the fact that using VALUES to specify literal tuples can be used with both INSERT and the FROM clause of a SELECT.

Is there any reason syntactically the SELECT needs to be required? If you use a VALUES table literal as a subselect, you have to give it column names with an AS clause (see https://www.postgresql.org/docs/current/sql-values.html). I can imagine a simpler syntax where you'd do WITH VALUES ... AS table_name (column_1_name, ...).

Is there any reason to alias the lookup table as `codes`?

There are apparently lots of other clever uses of the WITH clause, such as https://www.postgresql.org/docs/current/queries-with.html#QU...

CTEs are low-key one of the best features of SQL. Great for debugging big queries, such as:

  with source as (
    select * from wherever
  ),
  transformed as (
    ...
  ),
  joined as (
    ...
  ),
  final as (
    ...
  )
  select * from final

You can switch `final` to `transformed` to see what the query is doing internally. Almost like having good control flow. Almost.

Common table expression. I don't know exactly where the "common" part comes from, but I'm guessing because you can use its alias multiple times in the subsequent CTEs and the main query.

common table expression. It's basically like a view that can be defined at the beginning of a query, and can be referenced within that query.

Fun fact, this can also be more performant, depending on the engine.