liveBook · Manning

With clause

As explained in DuckDB in Action

The WITH clause defines one or more named temporary query results for use within a SQL statement.

A WITH clause defines named common table expressions (CTEs): temporary query results scoped to one SQL statement. CTEs isolate query logic without creating a persistent view and can reference earlier CTEs in the same clause. section 3.4.4

Use CTEs to make multi-stage queries easier to build and read:

  • Replace deeply nested subqueries in FROM with named intermediate relations. section 3.4.4
  • Chain transformations incrementally, such as hourly aggregation followed by filtering, joining, and maximum selection. section 3.4.4
  • Use WITH RECURSIVE when a query must follow parent-child relationships through a graph-shaped structure. section 3.4.4

Find readings at maximum power

WITH max_power AS ( SELECT max(power) AS v FROM readings ) SELECT max_power.v, read_on FROM max_power JOIN readings ON power = max_power.v; section 3.4.4

Listing 3.10 The structure of a SELECT statement
SELECT select_list
FROM tables
WHERE condition
GROUP BY groups
HAVING group_filter
ORDER BY order_expr
LIMIT n

Use the SELECT structure as a map for locating the WITH clause’s role before FROM, filtering, grouping, ordering, and limiting. section 3.4.4

How DuckDB in Action covers it

How deep it goes
Explains scope, CTE chaining, subquery replacement, aggregation pipelines, and recursive traversal without discussing execution planning.
Examples
Demonstrates maximum-power lookup, hourly power aggregation, and recursive traversal of a parent-child tree.
What it assumes
Assumes familiarity with SELECT, FROM, joins, grouping, and subqueries while showing how to restructure those operations.
What you can do after
Build it from scratch by composing readable, multi-stage SQL queries with ordinary and recursive CTEs.
How it connects
Connects common table expressions with views, subqueries, joins, aggregation, and recursive SQL.
Cover of DuckDB in Action
liveBookmanning.com
Read DuckDB in Action on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of DuckDB in Action. The section links point to the parts of the book it draws on.