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

Taught in

References