Common Table Expressions (WITH) in MySQL - Step-by-Step Execution

Concept Flow - Common Table Expressions (WITH)

Start Query

↓

Define CTE with WITH

↓

CTE Executes and Stores Result

↓

Main Query Uses CTE Result

↓

Return Final Result

↓

End Query

The query starts by defining a temporary named result (CTE) using WITH, then the main query uses this result to produce the final output.

Execution Sample

MySQL

WITH SalesCTE AS (
  SELECT salesperson, SUM(amount) AS total_sales
  FROM sales
  GROUP BY salesperson
)
SELECT salesperson, total_sales
FROM SalesCTE
WHERE total_sales > 1000;

This query calculates total sales per salesperson and then selects those with sales over 1000.

Execution Table

Step	Action	CTE Result	Main Query Action	Output Rows
1	Start query execution	None	None	None
2	Execute CTE: sum sales per salesperson	[{Alice, 1200}, {Bob, 900}, {Carol, 1500}]	None	None
3	Main query selects from CTE where total_sales > 1000	[{Alice, 1200}, {Bob, 900}, {Carol, 1500}]	Filter rows with total_sales > 1000	[{Alice, 1200}, {Carol, 1500}]
4	Return final result	[{Alice, 1200}, {Bob, 900}, {Carol, 1500}]	Output filtered rows	[{Alice, 1200}, {Carol, 1500}]
5	End query execution	None	None	Final output returned

💡 Query ends after main SELECT returns filtered rows from CTE.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
SalesCTE	None	[{Alice, 1200}, {Bob, 900}, {Carol, 1500}]	[{Alice, 1200}, {Bob, 900}, {Carol, 1500}]	[{Alice, 1200}, {Bob, 900}, {Carol, 1500}]
Output Rows	None	None	[{Alice, 1200}, {Carol, 1500}]	[{Alice, 1200}, {Carol, 1500}]

Key Moments - 2 Insights

Why does the main query only see the CTE result and not the original sales table?

Because the CTE acts like a temporary table created by the WITH clause, the main query uses this precomputed result instead of accessing the original table directly, as shown in execution_table step 3.

What happens if the CTE returns no rows?

If the CTE returns no rows, the main query will have no data to filter or return, resulting in an empty output, similar to execution_table step 3 but with zero rows.

Visual Quiz - 3 Questions

Look at the execution table, what is the content of SalesCTE after step 2?

A [{Alice, 900}, {Bob, 1200}, {Carol, 1500}]

B [{Alice, 1500}, {Bob, 900}, {Carol, 1200}]

C [{Alice, 1200}, {Bob, 900}, {Carol, 1500}]

D Empty

💡 Hint

Check the 'CTE Result' column in execution_table row for step 2.

At which step does the main query apply the filter total_sales > 1000?

A Step 3

B Step 4

C Step 2

D Step 5

💡 Hint

Look at the 'Main Query Action' column in execution_table to find when filtering happens.

If Bob's sales were 1100 instead of 900, how would the output rows change at step 4?

A [{Alice, 1200}, {Carol, 1500}]

B [{Alice, 1200}, {Bob, 1100}, {Carol, 1500}]

C [{Bob, 1100}]

D Empty

💡 Hint

Refer to variable_tracker Output Rows after step 3 and consider the filter condition.

Concept Snapshot

WITH clause defines a temporary named result called a CTE.
CTE runs first and stores its result.
Main query uses CTE like a table.
Useful for breaking complex queries into parts.
CTE exists only during query execution.

Full Transcript

This visual execution shows how a Common Table Expression (CTE) works in MySQL using the WITH clause. First, the CTE named SalesCTE calculates total sales per salesperson by summing amounts from the sales table. This result is stored temporarily. Then, the main query selects from SalesCTE only those salespersons whose total sales exceed 1000. The execution table traces each step: starting the query, executing the CTE, filtering in the main query, and returning the final output. The variable tracker shows how SalesCTE holds the grouped sales data and how output rows change after filtering. Key moments clarify that the main query uses the CTE result, not the original table, and what happens if the CTE returns no rows. The quiz tests understanding of the CTE content after execution, when filtering happens, and how changing data affects output. The snapshot summarizes that WITH creates a temporary named result used only during the query, helping organize complex queries into simpler parts.