WITH clause syntax in SQL - Step-by-Step Execution

Concept Flow - WITH clause syntax

Start Query

↓

Define CTE with WITH

↓

Use CTE in main query

↓

Execute main query

↓

Return results

↓

End

The WITH clause defines a temporary named result (CTE) used by the main query that follows.

Execution Sample

SQL

WITH SalesCTE AS (
  SELECT ProductID, SUM(Quantity) AS TotalSold
  FROM Sales
  GROUP BY ProductID
)
SELECT * FROM SalesCTE WHERE TotalSold > 100;

This query defines a CTE named SalesCTE that sums quantities sold per product, then selects products with sales over 100.

Execution Table

Step	Action	Evaluation	Result
1	Parse WITH clause	Define SalesCTE as grouped sum of Quantity by ProductID	SalesCTE ready with aggregated data
2	Execute CTE query	Calculate SUM(Quantity) per ProductID	Temporary table SalesCTE created
3	Execute main query	Select rows from SalesCTE where TotalSold > 100	Filtered rows with TotalSold > 100
4	Return results	Output filtered rows	Final result set returned
5	End	No more steps	Query execution complete

💡 Query ends after returning filtered results from the CTE

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
SalesCTE	undefined	Aggregated data by ProductID	Filtered rows with TotalSold > 100	Filtered result set

Key Moments - 3 Insights

Why do we define the CTE before the main query?

The CTE must be defined first (see execution_table step 1) so the main query can use its results.

Is the CTE data stored permanently?

No, the CTE is temporary and only exists during query execution (execution_table step 2).

Can the main query use the CTE multiple times?

Yes, the CTE acts like a temporary table accessible throughout the main query (execution_table step 3).

Visual Quiz - 3 Questions

Look at the execution table, what happens at step 2?

A The CTE query calculates aggregated sums

B The main query filters rows from SalesCTE

C The query ends and returns results

D The WITH clause is parsed but not executed

💡 Hint

Check the 'Action' and 'Evaluation' columns at step 2 in the execution_table

At which step does the query return the final results?

A Step 1

B Step 3

C Step 4

D Step 5

💡 Hint

Look for 'Return results' in the 'Action' column of execution_table

If the WHERE clause in the main query was removed, how would the variable SalesCTE change after step 3?

A It would be empty

B It would contain all aggregated rows without filtering

C It would contain only rows with TotalSold <= 100

D It would cause an error

💡 Hint

Refer to variable_tracker and execution_table step 3 about filtering

Concept Snapshot

WITH clause syntax:
WITH cte_name AS (
  subquery
)
SELECT * FROM cte_name;
- Defines a temporary named result (CTE)
- CTE is used by the main query
- CTE exists only during query execution

Full Transcript

The WITH clause in SQL lets you define a temporary named result called a Common Table Expression (CTE). First, the CTE is defined with a subquery that runs and creates a temporary table. Then, the main query uses this CTE as if it were a regular table. The CTE only exists during the query execution and helps organize complex queries by breaking them into parts. In the example, SalesCTE sums quantities sold per product. The main query then selects products with total sales over 100. The execution steps show parsing the WITH clause, running the CTE subquery, filtering results in the main query, and returning the final output.