CTE vs subquery vs view decision in SQL - Visual Side-by-Side Comparison

Concept Flow - CTE vs subquery vs view decision

Start Query

↓

Choose Method

↓

CTE

↓

Define

↓

Use in Main Query

↓

Execute & Return Result

This flow shows how a query starts, then you decide to use a CTE, subquery, or view, define or embed it, use it in the main query, and finally execute to get results.

Execution Sample

SQL

WITH TopSales AS (
  SELECT salesperson, SUM(sales) AS total
  FROM Sales
  GROUP BY salesperson
  HAVING SUM(sales) > 1000
)
SELECT * FROM TopSales;

This query uses a CTE named TopSales to find salespeople with total sales over 1000, then selects all from that CTE.

Execution Table

Step	Action	CTE/Subquery/View	Result/State
1	Start query execution	N/A	Query begins
2	Evaluate CTE definition	CTE	Calculate total sales per salesperson, filter > 1000
3	Store CTE result temporarily	CTE	CTE named TopSales holds filtered sales data
4	Main query selects from CTE	CTE	Retrieve all rows from TopSales
5	Return final result set	CTE	Rows of salespeople with sales > 1000
6	End execution	N/A	Query completes

💡 CTE evaluated first, then main query uses its result; execution ends after returning filtered rows.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
TopSales	undefined	calculated totals with filter	stored temporary result	used in main query	final result set returned

Key Moments - 3 Insights

Why does the CTE get evaluated before the main query?

Because the execution_table row 2 shows the CTE is calculated first to provide a temporary result for the main query to use in row 4.

How is a subquery different from a CTE in execution?

A subquery is embedded directly inside the main query and evaluated as part of that query, unlike the CTE which is defined first and then referenced, as seen in the concept_flow.

When should I use a view instead of a CTE or subquery?

Use a view when you want to save a query for reuse and simplify complex queries, as views are predefined and stored in the database, unlike CTEs or subqueries which are temporary and query-specific.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the CTE result stored temporarily?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Check the 'Result/State' column in execution_table row 3 for when the CTE is stored.

According to variable_tracker, what is the state of TopSales after step 4?

A Undefined

B Calculated totals with filter

C Used in main query

D Final result set returned

💡 Hint

Look at the 'After Step 4' column for TopSales in variable_tracker.

If you replaced the CTE with a subquery, what would change in the execution flow?

A The subquery would be evaluated as part of the main query execution

B The subquery would be evaluated after the main query

C The subquery would be stored temporarily like a CTE

D The query would not execute

💡 Hint

Refer to key_moments explanation about subquery evaluation timing.

Concept Snapshot

CTE vs Subquery vs View Decision:
- CTE: Defined first, temporary, reusable in query
- Subquery: Embedded inside main query, evaluated inline
- View: Predefined stored query, reusable across sessions
Choose CTE for readability, subquery for simple inline use, view for reuse and abstraction.

Full Transcript

This visual execution shows how a SQL query using a CTE runs step-by-step. First, the CTE is evaluated and its result stored temporarily. Then the main query uses this temporary result to produce the final output. Subqueries differ by being embedded and evaluated inline, while views are predefined stored queries for reuse. Understanding when each is evaluated helps decide which to use for clarity, performance, or reuse.