Combining CTEs and VALUES in SQLite#

Here’s how to use SQLite’s VALUES syntax with a CTE to create a temporary table that you can then perform joins against in a query:

1
with x(c1, c2, c3) as (
2
  values
3
    ('a', 'b', 3),
4
    ('b', 'c', 4)
5
)
6
select * from x

Try that here.

The output of this query is:

The with x(c1, c2, c3) bit defines a temporary table for the duration of the query called x with columns called c1, c2 and c3.

Then the values (...), (...) bit defines two rows within that table - and can define many more.

This is useful for injecting data that you can then join against other tables - or for providing queries that include their own example data to illustrate different SQL concepts.