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:

with x(c1, c2, c3) as (
    ('a', 'b', 3),
    ('b', 'c', 4)
select * from x

Try that here.

The output of this query is:

c1 c2 c3
a b 3
b c 4

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.

Created 2023-01-29T17:15:37-08:00, updated 2023-02-02T21:00:02-08:00 · History · Edit