ADVANCED

SQL CTEs

Name an intermediate result with WITH to make complex SQL easier to read.

After this lessonBreak a long analytical query into understandable steps.

How it works

A Common Table Expression (CTE) gives an intermediate query result a name for the duration of one statement. It is often cleaner than nesting several subqueries.

CTEs are especially useful when a query has logical stages such as filtering, summarizing and then ranking. Recursive CTEs can also traverse hierarchies, although they require extra care.

REAL WORK

Build a first step that summarizes PO-level data, then filter only POs with missing handling units in the outer query.

SYNTAX

The pattern

WITH Summary AS (
  SELECT ...
)
SELECT * FROM Summary;

Example

WITH CustomerOrders AS (
  SELECT CustomerID, COUNT(*) AS Orders
  FROM Orders
  GROUP BY CustomerID
)
SELECT * FROM CustomerOrders
WHERE Orders > 1;

TRY IT

Run the example

Edit the query and run it against the built-in demo tables. Nothing is sent to a server.

SELECT CustomerID, COUNT(*) AS Orders
FROM Orders
GROUP BY CustomerID;

The learning runner supports SELECT, WHERE, LIKE, IN, BETWEEN, GROUP BY, HAVING, JOIN, aggregates, ORDER BY and LIMIT. Advanced lessons explain additional production SQL concepts even when the mini runner does not execute that syntax.

QUICK CHECK

Which keyword starts a CTE?

Progress is stored only in this browser. No account required.