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 (
  values
    ('a', 'b', 3),
    ('b', 'c', 4)
)
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.

Related

• sqlite The simplest recursive CTE - 2022-03-20
• sqlite SQLite aggregate filter clauses - 2021-08-04
• sqlite Splitting on commas in SQLite - 2021-02-01
• sqlite List all columns in a SQLite database - 2020-05-06
• sqlite Ordered group_concat() in SQLite - 2022-02-06
• django Django data migration using a PostgreSQL CTE - 2021-05-17
• sqlite Subqueries in select expressions in SQLite - also window functions - 2023-02-08
• sqlite SQLite can use more than one index for a query - 2022-12-11
• sqlite Lag window function in SQLite - 2020-04-19
• sqlite Returning related rows in a single SQL query using JSON - 2022-09-16

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