Querying with SQL: joins, grouping, upserts and CTEs

Joins and aggregates in SQLite syntax, ON CONFLICT upserts with RETURNING, CTEs and recursive queries, and window functions.

Joins, grouping and window functions

select b.title,
       a.name                       as author,
       count(r.id)                  as reviews,
       round(avg(r.rating), 2)      as avg_rating
from book b
join author a on a.id = b.author_id
left join review r on r.book_id = b.id
group by b.id
having count(r.id) > 0
order by avg_rating desc, b.title
limit 20;

-- top 2 books per author with a window function
select author, title, published
from (
  select a.name as author, b.title, b.published,
         row_number() over (partition by b.author_id order by b.published desc) as rn
  from book b join author a on a.id = b.author_id
)
where rn <= 2;

-- a running total
select id, amount,
       sum(amount) over (order by id rows between unbounded preceding and current row) as balance
from ledger;

-- upsert with a conflict target and a conditional update
insert into book (isbn, title, updated_at)
values (?1, ?2, unixepoch())
on conflict (isbn) do update set
  title = excluded.title,
  updated_at = excluded.updated_at
where excluded.updated_at > book.updated_at
returning id, title;

• Upsert targets a specific conflict. Without a matching unique index it raises a syntax error rather than falling back to a plain insert.
• The where clause on do update makes the upsert conditional: if the condition is false the row is left alone and nothing is returned.
• SQLite has no full outer join before 3.39. Emulate it with a left join plus a union of the right-side-only rows.
• returning removes the need for a last_insert_rowid() call and works with multi-row inserts.

CTEs and recursion

with recent as (
  select * from invoice where issued_at >= strftime('%s', 'now', '-30 days')
),
by_customer as (
  select customer_id, sum(total) as revenue, count(*) as invoices
  from recent group by customer_id
)
select c.name, bc.revenue, bc.invoices
from by_customer bc join customer c on c.id = bc.customer_id
order by bc.revenue desc;

-- recursive: walk a category tree with a depth limit
with recursive tree(id, name, parent_id, depth, path) as (
  select id, name, parent_id, 1, name
  from category where parent_id is null
  union all
  select c.id, c.name, c.parent_id, t.depth + 1, t.path || ' / ' || c.name
  from category c join tree t on c.parent_id = t.id
  where t.depth < 10
)
select depth, path from tree order by path;

-- generate a date series without a numbers table
with recursive days(d) as (
  select date('2026-09-01')
  union all
  select date(d, '+1 day') from days where d < '2026-09-30'
)
select d from days;

• A CTE is materialised unless it is a simple, non-recursive, single-use query that the planner can inline. Add materialized or not materialized to be explicit.
• Recursion must terminate. A cycle in the data makes the query run until it hits memory or is interrupted, so include a depth guard even when the schema looks acyclic.
• Building a path with || grows the row size with depth; for deep trees keep the depth column and reconstruct the path in the application.
• SQLite has no with ... update in the same statement in older versions; check the version before relying on modern CTE syntax.

Practical query habits

• Always bind parameters. A ? placeholder reuses the prepared statement from the cache; an interpolated literal creates a new statement each time and re-parses it.
• Use explain query plan before assuming an index is used. Every line that says SCAN is a table scan.
• limit without order by is non-deterministic. Add a unique tie-breaker so pagination is stable.
• Compare dates as integers with unixepoch() or ISO strings with strftime(); mixing the two is the most common date bug in SQLite.
• Prefer one query with a join over a loop of queries. Each statement in SQLite has overhead, and the loop becomes the dominant cost.

-- the same statement, prepared once and reused with different bindings
-- sqlite3_prepare_v2(db, "select id from book where isbn = ?1", ...)
-- sqlite3_bind_text(stmt, 1, isbn, -1, SQLITE_TRANSIENT)
-- sqlite3_step(stmt)
-- sqlite3_reset(stmt); sqlite3_clear_bindings(stmt);   -- reuse, do not finalize

explain query plan
select id from book where isbn = '9780441013593';
-- SEARCH book USING INDEX sqlite_autoindex_book_1 (isbn=?)

FAQ

Related

Data types, affinity, STRICT tables and WITHOUT ROWID Indexes, ANALYZE and EXPLAIN QUERY PLAN

Last refreshed 2026-09-18.