CTEs (WITH clause)

Concepts covered: Common Table Expressions

A CTE is a named result set you define once at the top of a query, then reference by name anywhere below it. This works exactly the way you assign an intermediate variable in code before using it in a calculation. The filter logic lives inside the CTE, the main query reads from the named result, and the two steps stay cleanly separated.

CTE examples

The examples below start with a single CTE and then chain several in one query.

Basic example

A CTE starts with WITH, followed by a name and query definition. The main query then references the CTE by name.

WITH active_set AS (SELECT id, name FROM users WHERE active = 1) SELECT id, name FROM active_set WHERE id > 100;

The CTE active_set filters for active users. The main query then filters for IDs greater than 100. This separates filtering steps, making the logic easier to follow.

Multiple CTEs

You can define several CTEs in a single query, separating them with commas. Each CTE can reference the ones declared before it, so a long transformation breaks into named stages.

WITH users_set AS (SELECT id FROM users WHERE active = 1), orders_set AS (SELECT user_id FROM orders WHERE status = 'pending') SELECT id FROM users_set WHERE id IN (SELECT user_id FROM orders_set);

Each CTE produces a temporary dataset for the next one to read, so a complex query gets built one step at a time.

Practice: CTE basics

Multi-stage CTE queries

CTEs pay off when a query has several stages of filtering or computation, because you can name each stage and test it on its own.

Example: devices with high error counts

Take logs from many devices where you want to track the devices with very high error counts. The first stage computes error counts for every device, and the second selects only the most critical ones. Each stage has a name, and you can reuse its result for additional queries or checks.

CTEs exist only during query execution and cannot be accessed outside it. They can be referenced multiple times without recalculation, and must be declared in order; each CTE can only use datasets defined before it.

Good CTE names

• active_users describes the filtered set
• monthly_revenue names the computation
• error_devices clarifies the scope

Names to avoid

• temp says nothing about the data
• cte1 forces readers to trace the query
• data is too generic to be useful

Subqueries compared with CTEs

A subquery and a CTE can compute the same intermediate result and return the same rows.

Subqueries vs. CTEs

CTEs are easier to read and maintain once a query has more than one stage, because each intermediate dataset gets a name and can be referenced again.

Subquery version

With a subquery, the inner query is embedded directly in the WHERE clause. This works well for simple filters but becomes harder to read as complexity grows.

SELECT id FROM orders WHERE user_id IN (SELECT id FROM users WHERE active = 1);

CTE version

The CTE version moves the filter logic to a named block at the top, and the main query becomes a plain read from that name.

WITH active_users AS (SELECT id FROM users WHERE active = 1) SELECT id FROM orders WHERE user_id IN (SELECT id FROM active_users);

Both queries return the same rows.

Example: filtering device logs

When processing device logs, you often filter by valid services or active devices. A subquery can perform the same filtering inline, but a CTE names the intermediate dataset and allows multiple references, so a change to the filter happens in one place and you can check that stage on its own.

Which approach is easier to maintain when the same filtered set is needed twice?

Subquery

The same subquery appears twice. If the filter changes, you must update both copies, which is error-prone.

SELECT COUNT(*) FROM (SELECT id FROM users WHERE active = 1) a UNION ALL SELECT AVG(id) FROM (SELECT id FROM users WHERE active = 1) b

CTE

The CTE defines the filter once and both references share it. Changes only need to happen in one place.

WITH active AS (SELECT id FROM users WHERE active = 1) SELECT COUNT(*) FROM active UNION ALL SELECT AVG(id) FROM active

About this lesson

This section is part of the Query Structure: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.