A common table expression, or CTE, names a query result with WITH so it can be used within the single statement that follows.
A long query becomes easier to inspect when its steps have meaningful names. A CTE can define paid sales, then customer totals, then the final report. It expresses logical stages without creating permanent tables.
This reference adds detail to the main course. You can read it independently: the small database below supplies every table and row used in the examples. Start with the question each query answers, then compare the returned values with the data rather than memorising a line of syntax.
Set up the practice database
Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.
Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(40) NOT NULL,
city VARCHAR(40),
manager_id INTEGER
);
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount DECIMAL(10,2),
status VARCHAR(20) NOT NULL,
created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
(1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
(3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
(101,1,10,'paid','2026-01-01'),
(102,1,30,'pending','2026-01-02'),
(103,2,20,'paid','2026-01-02'),
(104,3,NULL,'pending','2026-02-01'),
(105,3,20,'paid','2026-02-02'),
(106,NULL,5,'paid','2026-02-03');An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.
The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.
How Common table expressions works
WITH paid_sales AS (SELECT ... ) SELECT ... FROM paid_sales defines one named result. The parentheses enclose the defining query, and the following statement consumes it. A CTE’s lifetime is that statement, not the database connection. A later independent SELECT cannot normally refer to paid_sales unless it defines the CTE again. Use a temporary table or view when a different lifetime is required.
Several CTEs are separated by commas after a single WITH. A later CTE can usually refer to one defined earlier in the same list, which supports a readable transformation chain. Give each stage a clear row grain: paid_sales has one row per sale, customer_totals has one per customer link, and the final join has one per customer. Naming stages by their actual shape makes it easier to review whether a join or aggregate changed the question.
Columns and scope of a CTE
The CTE exposes its selected output columns, not every field from the underlying source. An optional explicit CTE column list can assign names, while expression aliases in the defining SELECT often provide a simpler clear interface. Use CTEs to filter window results at an outer level or isolate repeated subquery logic. Avoid adding numerous stages that merely rename the same unchanged columns without clarifying a real decision.
CTEs do not guarantee a physical temporary result or improved performance. PostgreSQL has documented materialisation and folding behaviour with optional controls; SQL Server commonly expands CTE definitions rather than providing a general cached intermediate table. MySQL and SQLite also choose local optimisation strategies. Reusing a CTE name may or may not imply reuse of a stored result. Measure the plan and execution costs when the query matters operationally. Recursive CTEs are a separate feature with additional syntax and termination rules, rather than an automatic capability of any ordinary WITH definition.
MySQL, PostgreSQL, SQL Server and SQLite
Ordinary CTEs are supported by PostgreSQL, MySQL 8+, SQL Server and modern SQLite. In SQL Server, ensure the preceding statement is terminated before WITH; many scripts use ;WITH defensively. Materialisation hints and optimiser behaviour are engine-specific, so the shared example avoids those controls.
Check the engine-specific syntax and version requirements in PostgreSQL WITH reference.
Practice task and worked solution
Use one CTE to group all identified sale rows by customer and then filter customers with at least two sale records.
Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.
WITH counts AS (
SELECT customer_id,COUNT(*) AS sale_count FROM sales
WHERE customer_id IS NOT NULL GROUP BY customer_id
)
SELECT customer_id,sale_count FROM counts
WHERE sale_count>=2 ORDER BY customer_id;customer_id | sale_count
1 | 2
3 | 2The defining query produces customer-level counts. The outer WHERE can use sale_count because it is an exposed CTE column, not a same-level aggregate alias. Ana and Cara meet the threshold, including Cara’s unknown-amount record because the metric counts sales.
Run a second standalone SELECT from paid_sales after the example and observe that its CTE name is out of scope. Then temporarily change the paid filter to pending and inspect how the customer totals change. Restore the original condition and compare each stage’s grain, ensuring an outer customer label never turns the detail stage into an accidental additional grouping dimension.
SQL Common Table Expressions: WITH, Scope and Chaining Syntax
Common table expressions
WITH paid_sales AS (
SELECT customer_id,amount FROM sales WHERE status='paid'
), customer_totals AS (
SELECT customer_id,SUM(amount) AS paid_total
FROM paid_sales GROUP BY customer_id
)
SELECT c.id,c.name,t.paid_total FROM customers AS c
LEFT JOIN customer_totals AS t ON t.customer_id=c.id ORDER BY c.id;SQL Common Table Expressions: WITH, Scope and Chaining Summary
WITH name AS (SELECT ...)names a query result for use in the single statement that follows.- Several CTEs are separated by commas, and each can use the ones defined before it.
- A CTE disappears when its statement ends; a separate later query can't refer to it.
- A CTE exposes only the columns its query selects.
- CTEs improve structure; whether they speed a query up depends on the optimiser.
SQL Common Table Expressions: WITH, Scope and Chaining Examples
Each example shows the code, the output it produces and what each part does.
Paid totals in named stages
The first stage filters paid sale details. The second reduces those details to customer totals. The final left join restores the full customer population. The anonymous paid sale has its own NULL-link total but matches none of the four identified customers.
WITH paid_sales AS (
SELECT customer_id,amount FROM sales WHERE status='paid'
), customer_totals AS (
SELECT customer_id,SUM(amount) AS paid_total
FROM paid_sales GROUP BY customer_id
)
SELECT c.id,c.name,t.paid_total FROM customers AS c
LEFT JOIN customer_totals AS t ON t.customer_id=c.id ORDER BY c.id;id | name | paid_total
1 | Ana | 10
2 | Ben | 20
3 | Cara | 20
4 | Dev | NULLCommon SQL Common Table Expressions: WITH, Scope and Chaining Mistakes
Using a CTE in a later statement
Running SELECT * FROM paid_sales as a second independent statement fails because the CTE is out of scope. Another mistake is treating a CTE as a guaranteed cache: repeated references can have engine-dependent execution behaviour. Choose a real temporary stage if the workflow requires stored data across statements.
SQL Common Table Expressions: WITH, Scope and Chaining in Real Projects
Monthly reports built in stages
A monthly reporting query can define the eligible period, calculate customer measures and attach descriptive labels as named stages. This gives reviewers places to check intermediate row counts and assumptions. Keep each name tied to a substantive transformation so the structure improves comprehension rather than merely lengthening the query.
Name each CTE for its actual transformation and row grain. A paid-sale stage, a customer-total stage and a full-customer report are useful boundaries to review. If the workflow requires the prepared result across several statements, a temporary table may fit better. Do not choose a CTE solely because its name suggests a stored reusable cache.
SQL Common Table Expressions: WITH, Scope and Chaining FAQ
Is a CTE the same as a view?
No. A view is a persistent named definition stored in the database and usable by any later query; a CTE belongs to one statement. A CTE is a good way to try a definition before turning it into a view.
Does WITH always speed up a query?
No. It mainly provides structure; execution depends on optimisation and data. Some databases inline a CTE like a subquery, others may compute it once, and PostgreSQL lets you choose with MATERIALIZED or NOT MATERIALIZED.
Can CTEs refer to other CTEs?
Yes. In a normal ordered list, later definitions can consume earlier ones, so customer_totals can read from paid_sales. Referring forward to a CTE defined later isn't allowed, and recursive CTEs have their own rules, covered in the recursive queries lesson.
Why does SQL Server code write ;WITH?
In T-SQL, WITH can also start other clauses, so the statement before a CTE must end with a semicolon. Writing ;WITH defensively guarantees that, even when the previous line forgot its semicolon. Ending every statement with ; makes the trick unnecessary.
Can a CTE be used with INSERT, UPDATE or DELETE?
Yes, in PostgreSQL, SQLite and SQL Server, and in MySQL for UPDATE and DELETE. Define the rows to change in the WITH part, then use them in the statement, for example to delete the sales listed by a CTE of duplicates.
SQL Common Table Expressions: WITH, Scope and Chaining Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Which customer ids pass sale_count>=2 in the worked CTE exercise?
SQL Common Table Expressions: WITH, Scope and Chaining Quiz
2 questions to check what you learned.
How long is an ordinary CTE name available?
- For the statement containing its WITH clause
- For the entire connection until it disconnects
- Permanently for every connection after the first WITH
What is the grain of customer_totals in the example?
- One row per individual paid sale
- One row per customer link with paid sales
- One row for the complete database regardless of customer link