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.

Practice

1. What is the main purpose of using a WITH clause (Common Table Expression) in a MySQL query?

easy

A. To permanently store data in a new table

B. To create a temporary named result set that can be referenced within the main query

C. To delete rows from a table

D. To create an index on a table column

Solution

1. Step 1: Understand the role of WITH clause

   The WITH clause defines a temporary named result set, called a Common Table Expression (CTE), which exists only during the execution of the query.

2. Step 2: Differentiate from permanent operations

   Unlike creating tables or indexes, CTEs do not store data permanently but help organize complex queries by breaking them into parts.

3. Final Answer:

   To create a temporary named result set that can be referenced within the main query -> Option B

4. Quick Check:

   WITH clause = temporary named result set [OK]

Hint: WITH creates temporary named results for easier queries [OK]

Common Mistakes:

• Thinking WITH creates permanent tables
• Confusing WITH with DELETE or UPDATE commands
• Assuming WITH creates indexes

2. Which of the following is the correct syntax to start a Common Table Expression in MySQL?

easy

A. DEFINE cte_name AS SELECT * FROM table_name

B. CREATE CTE cte_name SELECT * FROM table_name

C. START CTE cte_name (SELECT * FROM table_name)

D. WITH cte_name AS (SELECT * FROM table_name)

Solution

1. Step 1: Recall the standard syntax for CTE

   The correct syntax to define a CTE starts with the keyword WITH, followed by the CTE name, AS, and then the query in parentheses.

2. Step 2: Compare options with syntax

   Only WITH cte_name AS (SELECT * FROM table_name) matches the correct syntax: WITH cte_name AS (SELECT * FROM table_name). Other options use invalid keywords or structure.

3. Final Answer:

   WITH cte_name AS (SELECT * FROM table_name) -> Option D

4. Quick Check:

   WITH + name + AS + (query) = correct syntax [OK]

Hint: WITH cte_name AS (query) is the standard start [OK]

Common Mistakes:

• Using CREATE or DEFINE instead of WITH
• Omitting AS keyword
• Not enclosing query in parentheses

3. Given the following query, what will be the output?

WITH SalesCTE AS (
  SELECT product_id, SUM(quantity) AS total_qty
  FROM sales
  GROUP BY product_id
)
SELECT * FROM SalesCTE WHERE total_qty > 100;

medium

A. All products with total quantity sold greater than 100

B. All products with total quantity sold less than or equal to 100

C. Syntax error due to missing GROUP BY

D. Empty result because CTE is not used properly

Solution

1. Step 1: Analyze the CTE definition

   The CTE named SalesCTE calculates total quantity sold per product by grouping sales by product_id and summing quantity.

2. Step 2: Understand the main query filter

   The main query selects rows from SalesCTE where total_qty is greater than 100, so only products with sales above 100 units appear.

3. Final Answer:

   All products with total quantity sold greater than 100 -> Option A

4. Quick Check:

   CTE sums and filters total_qty > 100 [OK]

Hint: CTE calculates sums; main query filters by condition [OK]

Common Mistakes:

• Confusing > 100 with <= 100
• Thinking GROUP BY is missing (it is present)
• Assuming CTE is unused or causes error

4. Identify the error in the following MySQL query using a CTE:

WITH cte AS (
  SELECT id, name FROM users
)
SELECT * FROM cte WHERE age > 30;

medium

A. CTE query missing a semicolon after closing parenthesis

B. WITH clause must be followed by INSERT statement

C. Column 'age' does not exist in the CTE result

D. CTE name cannot be 'cte'

Solution

1. Step 1: Check columns selected in CTE

   The CTE selects only 'id' and 'name' columns from users table; 'age' is not included.

2. Step 2: Analyze main query filter

   The main query tries to filter on 'age', which is not part of the CTE result, causing an error.

3. Final Answer:

   Column 'age' does not exist in the CTE result -> Option C

4. Quick Check:

   Filter column must be in CTE output [OK]

Hint: Filter columns must be selected in CTE first [OK]

Common Mistakes:

• Assuming semicolon needed inside CTE
• Thinking CTE name 'cte' is invalid
• Believing WITH must precede INSERT

5. You want to find the top 3 customers by total purchase amount using a CTE. Which query correctly uses a CTE to achieve this?

hard

A. WITH CustomerTotals AS (
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
)
SELECT customer_id, total_amount
FROM CustomerTotals
ORDER BY total_amount DESC
LIMIT 3;

B. WITH CustomerTotals AS (
SELECT customer_id, amount
FROM orders
)
SELECT customer_id, SUM(amount) AS total_amount
FROM CustomerTotals
GROUP BY customer_id
ORDER BY total_amount DESC
LIMIT 3;

C. WITH CustomerTotals AS (
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
)
SELECT * FROM CustomerTotals
LIMIT 3;

D. WITH CustomerTotals AS (
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
ORDER BY total_amount DESC
LIMIT 3
)
SELECT * FROM CustomerTotals;

Solution

1. Step 1: Check CTE calculation and grouping

   WITH CustomerTotals AS ( SELECT customer_id, SUM(amount) AS total_amount FROM orders GROUP BY customer_id ) SELECT customer_id, total_amount FROM CustomerTotals ORDER BY total_amount DESC LIMIT 3; correctly groups orders by customer_id and sums amount inside the CTE.

2. Step 2: Verify ordering and limiting in main query

   WITH CustomerTotals AS ( SELECT customer_id, SUM(amount) AS total_amount FROM orders GROUP BY customer_id ) SELECT customer_id, total_amount FROM CustomerTotals ORDER BY total_amount DESC LIMIT 3; orders by total_amount descending and limits to top 3 customers outside the CTE, which is valid in MySQL.

3. Step 3: Analyze other options for errors

   WITH CustomerTotals AS ( SELECT customer_id, amount FROM orders ) SELECT customer_id, SUM(amount) AS total_amount FROM CustomerTotals GROUP BY customer_id ORDER BY total_amount DESC LIMIT 3; does not sum inside CTE, causing incorrect grouping. WITH CustomerTotals AS ( SELECT customer_id, SUM(amount) AS total_amount FROM orders ) SELECT * FROM CustomerTotals LIMIT 3; misses GROUP BY and ordering. WITH CustomerTotals AS ( SELECT customer_id, SUM(amount) AS total_amount FROM orders GROUP BY customer_id ORDER BY total_amount DESC LIMIT 3 ) SELECT * FROM CustomerTotals; tries to use ORDER BY and LIMIT inside CTE, which is invalid in MySQL.

4. Final Answer:

   Option A query correctly finds top 3 customers by total purchase amount -> Option A

5. Quick Check:

   Group and sum in CTE, order and limit outside [OK]

Hint: Group and sum in CTE; order and limit outside [OK]

Common Mistakes:

• Placing ORDER BY and LIMIT inside CTE
• Not grouping before summing
• Trying to sum outside CTE without grouping