A common table expression (CTE) is a named query fragment defined with WITH syntax and consumed by a larger SQL statement. It breaks complicated joins, filters, and subqueries into readable pieces, and the same result can be referenced more than once. section 2.3
A practical use is finding customers who received invoice reminders for unpaid service orders. Separate CTEs can isolate unpaid invoices and orders whose item is NULL, then the main query joins those results to contact and email tables. section 2.3
Readable invoice-reminder query
WITH unp AS (... unpaid invoices ...), ni AS (... service orders ...) SELECT DISTINCT email FROM customer_contact_details ccd JOIN unp ON unp.c = ccd.id JOIN ni ON ni.id = unp.og JOIN sent_emails se ON se.invoice = unp.id AND se.email_type = 'Invoice reminder'; Compared with nested subqueries, this structure makes each intermediate result explicit. EXPLAIN ANALYZE produced a simpler plan with faster planning and execution for this example. section 2.3
Treat readability as the primary benefit rather than assuming every CTE is faster. PostgreSQL’s optimizer may merge and reorder query parts, so inspect the resulting plan when performance matters. section 2.11 section 2.3
