common table expression
A named intermediate result, written in a WITH clause before the main query, that later parts of the query can use like a table.
In more detail
CTE stands for common table expression. Each step can be read and tested on its own, and later steps can use earlier names, which turns a long query into a readable pipeline. In SQLite and modern PostgreSQL it is mainly a readability tool rather than a performance one.
Example
WITH paid_orders AS (SELECT * FROM orders WHERE status = 'paid') SELECT COUNT(*) FROM paid_orders;Related terms
- recursive CTE — A common table expression that refers to its own name, used to walk trees and paths such as an organisation chart.
- subquery — A query written in brackets inside another query, whose result the outer query uses.
- grain — What exactly one row of a result, table or report represents, such as one order, one order line or one customer per month.
Taught in
- Aggregate Functions — PostgreSQL, Indexing & Relational Data Modeling
- Grouping By Multiple Columns — PostgreSQL, Indexing & Relational Data Modeling
- Common Table Expressions — PostgreSQL, Indexing & Relational Data Modeling