CTE as readable subquery replacement in SQL - Step-by-Step Execution

Concept Flow - CTE as readable subquery replacement

Start Query

↓

Define CTE

↓

CTE Executes First

↓

Main Query Uses CTE Result

↓

Return Final Result

The query starts by defining a CTE, which runs first and acts like a named temporary result. Then the main query uses this CTE instead of a subquery, making the SQL easier to read.

Execution Sample

SQL

WITH RecentOrders AS (
  SELECT CustomerID, OrderDate
  FROM Orders
  WHERE OrderDate > '2024-01-01'
)
SELECT CustomerID FROM RecentOrders;

This query defines a CTE named RecentOrders to get orders after 2024-01-01, then selects CustomerID from that CTE.

Execution Table

Step	Action	Evaluation	Result
1	Define CTE RecentOrders	Select orders with OrderDate > '2024-01-01'	Temporary table with filtered orders
2	Execute CTE	Run the CTE query first	RecentOrders table created
3	Main query selects CustomerID	Select CustomerID from RecentOrders	List of CustomerIDs from filtered orders
4	Return final result	Output CustomerID list	Final result set with CustomerIDs

💡 Query ends after main SELECT returns CustomerIDs from CTE

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
RecentOrders	undefined	Defined as filtered orders	Executed and stored	Used in main query	No longer needed after query

Key Moments - 3 Insights

Why does the CTE run before the main query?

Because the CTE is like a temporary table defined at the start, it must be executed first to provide data for the main query, as shown in execution_table step 2.

Is the CTE stored permanently in the database?

No, the CTE exists only during the query execution and disappears after, similar to a temporary snapshot, as seen in variable_tracker final state.

How does using a CTE improve readability compared to a subquery?

CTEs give a name to the subquery and separate it from the main query, making the SQL easier to read and maintain, as shown by the clear steps in execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the CTE actually executed?

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

Check the 'Action' and 'Evaluation' columns in execution_table rows to see when the CTE runs.

According to variable_tracker, what happens to the CTE after the query finishes?

A It remains stored permanently

B It disappears after query execution

C It is used again in another query automatically

D It becomes a permanent table

💡 Hint

Look at the 'Final' column for RecentOrders in variable_tracker.

If we replaced the CTE with a subquery inside the main SELECT, what would change in the execution flow?

A The subquery runs before the CTE

B The subquery runs after the main query

C The subquery runs multiple times if referenced multiple times

D The subquery is stored permanently

💡 Hint

Think about how subqueries execute compared to CTEs, referencing the concept_flow and execution_table.

Concept Snapshot

CTE syntax:
WITH cte_name AS (subquery)
SELECT ... FROM cte_name;

CTEs run first, act like named temporary tables.
They improve readability by separating logic.
CTEs exist only during query execution.

Full Transcript

This visual execution shows how a Common Table Expression (CTE) works as a readable replacement for subqueries in SQL. First, the CTE is defined with a name and a query inside parentheses. This CTE runs before the main query and creates a temporary result set. Then the main query uses this named result as if it were a table. The execution table traces each step: defining the CTE, executing it, running the main query using the CTE, and returning the final result. The variable tracker shows the CTE's lifecycle from undefined to defined, executed, used, and then removed after the query finishes. Key moments clarify common confusions like why the CTE runs first, that it is temporary, and how it improves readability. The quiz tests understanding of when the CTE runs, its temporary nature, and differences from subqueries. The snapshot summarizes the syntax and behavior for quick reference.

Practice

1. What is the main benefit of using a WITH clause (CTE) instead of a nested subquery in SQL?

easy

A. It always runs faster than nested subqueries.

B. It makes the query easier to read and maintain by naming parts of the query.

C. It removes the need for any JOIN operations.

D. It automatically indexes the tables used in the query.

Solution

1. Step 1: Understand the purpose of CTEs

   CTEs (Common Table Expressions) allow you to name a subquery, making the SQL easier to read and organize.

2. Step 2: Compare with nested subqueries

   Nested subqueries can be hard to read because they are embedded inside other queries without a clear name.

3. Final Answer:

   It makes the query easier to read and maintain by naming parts of the query. -> Option B

4. Quick Check:

   CTEs improve readability = A [OK]

Hint: CTEs name subqueries for clarity and maintenance [OK]

Common Mistakes:

• Thinking CTEs always improve performance
• Believing CTEs remove the need for JOINs
• Assuming CTEs create indexes automatically

2. Which of the following is the correct syntax to start a CTE in SQL?

easy

A. WITH cte_name AS (SELECT ...) SELECT * FROM cte_name;

B. DEFINE cte_name AS (SELECT ...) SELECT * FROM cte_name;

C. CREATE CTE cte_name AS SELECT ...;

D. SELECT * FROM WITH cte_name AS (SELECT ...)

Solution

1. Step 1: Recall the standard CTE syntax

   The correct syntax starts with WITH cte_name AS (subquery), followed by the main query.

2. Step 2: Check each option

   WITH cte_name AS (SELECT ...) SELECT * FROM cte_name; correctly shows the CTE definition and usage. Options A, B, and C use invalid keywords or order.

3. Final Answer:

   WITH cte_name AS (SELECT ...) SELECT * FROM cte_name; -> Option A

4. Quick Check:

   CTE syntax starts with WITH = D [OK]

Hint: CTE always starts with WITH keyword followed by AS [OK]

Common Mistakes:

• Placing WITH after SELECT
• Using CREATE or DEFINE keywords for CTE
• Missing AS keyword after CTE name

3. Given the tables orders(order_id, customer_id, amount) and customers(customer_id, name), what will this query return?

WITH high_orders AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
  HAVING SUM(amount) > 100
)
SELECT c.name, h.total
FROM high_orders h
JOIN customers c ON h.customer_id = c.customer_id;

medium

A. Names and total order amounts for customers with orders over 100 in total.

B. All customers with their total order amounts, regardless of amount.

C. Only customers with a single order over 100.

D. An error because CTEs cannot use GROUP BY.

Solution

1. Step 1: Analyze the CTE definition

   The CTE high_orders groups orders by customer and filters those with total amount > 100.

2. Step 2: Understand the main query

   The main query joins high_orders with customers to get customer names and their total order amounts.

3. Final Answer:

   Names and total order amounts for customers with orders over 100 in total. -> Option A

4. Quick Check:

   CTE filters totals > 100, join adds names = C [OK]

Hint: CTE filters first, main query joins for details [OK]

Common Mistakes:

• Confusing HAVING with WHERE
• Thinking it returns all customers
• Believing CTEs cannot use GROUP BY

4. Identify the error in this SQL query using a CTE:

WITH recent_orders AS (
  SELECT order_id, order_date
  FROM orders
  WHERE order_date > '2023-01-01'
)
SELECT *
FROM recent_orders
WHERE order_date < '2023-01-01';

medium

A. CTEs cannot be used with WHERE clauses inside them.

B. The CTE name cannot be used in the main query.

C. The query is missing a semicolon after the CTE definition.

D. The CTE filters orders after 2023-01-01 but main query filters before, resulting in no rows.

Solution

1. Step 1: Check the CTE filter condition

   The CTE selects orders with order_date > '2023-01-01'.

2. Step 2: Check the main query filter

   The main query filters order_date < '2023-01-01', which contradicts the CTE filter, so no rows match.

3. Final Answer:

   The CTE filters orders after 2023-01-01 but main query filters before, resulting in no rows. -> Option D

4. Quick Check:

   Conflicting filters cause empty result = B [OK]

Hint: Check filter conditions inside and outside CTE for conflicts [OK]

Common Mistakes:

• Thinking WHERE is not allowed in CTE
• Expecting semicolon after WITH clause
• Believing CTE names are invalid in main query

5. You want to find customers who placed orders totaling more than 500, but only include those customers who have placed at least 3 orders. Which CTE-based query correctly achieves this?

hard

A. WITH order_summary AS (
SELECT customer_id, COUNT(order_id) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) >= 3
)
SELECT * FROM order_summary WHERE total_amount > 500;

B. WITH order_summary AS (
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 500
)
SELECT * FROM order_summary WHERE order_count >= 3;

C. WITH order_summary AS (
SELECT customer_id, COUNT(order_id) AS order_count, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 500 AND COUNT(order_id) >= 3
)
SELECT * FROM order_summary;

D. WITH order_summary AS (
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
)
SELECT * FROM order_summary WHERE total_amount > 500 AND order_count >= 3;

Solution

1. Step 1: Understand the requirements

   We need customers with total order amount > 500 and at least 3 orders.

2. Step 2: Check each option's logic

   WITH order_summary AS ( SELECT customer_id, COUNT(order_id) AS order_count, SUM(amount) AS total_amount FROM orders GROUP BY customer_id HAVING SUM(amount) > 500 AND COUNT(order_id) >= 3 ) SELECT * FROM order_summary; calculates both count and sum in the CTE and filters with HAVING both conditions, which is correct. Options B, C, and D try to filter outside the CTE on columns not selected or missing.

3. Final Answer:

   WITH order_summary AS ( SELECT customer_id, COUNT(order_id) AS order_count, SUM(amount) AS total_amount FROM orders GROUP BY customer_id HAVING SUM(amount) > 500 AND COUNT(order_id) >= 3 ) SELECT * FROM order_summary; -> Option C

4. Quick Check:

   Filter both conditions inside CTE HAVING = A [OK]

Hint: Use HAVING to filter grouped results inside CTE [OK]

Common Mistakes:

• Filtering aggregated columns outside CTE
• Missing COUNT or SUM in CTE select
• Using WHERE instead of HAVING for aggregates