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.

Practice

1. Which SQL feature is best used to temporarily break down a complex query into simpler parts for easier reading and maintenance?

easy

A. Common Table Expression (CTE)

B. View

C. Subquery

D. Index

Solution

1. Step 1: Understand the purpose of CTEs

   CTEs allow you to define temporary named result sets that can be referenced within a query, making complex queries easier to read and maintain.

2. Step 2: Compare with other options

   Views are permanent saved queries, subqueries are nested queries but can be harder to read, and indexes are for performance, not query structure.

3. Final Answer:

   Common Table Expression (CTE) -> Option A

4. Quick Check:

   Temporary query breakdown = CTE [OK]

Hint: Temporary query parts? Use CTEs [OK]

Common Mistakes:

• Confusing views as temporary structures
• Thinking subqueries improve readability as much as CTEs
• Assuming indexes help with query structure

2. Which of the following is the correct syntax to define a Common Table Expression (CTE) in SQL?

easy

A. CREATE VIEW cte_name AS SELECT * FROM table_name;

B. WITH cte_name AS (SELECT * FROM table_name) SELECT * FROM cte_name;

C. SELECT * FROM (SELECT * FROM table_name) AS cte_name;

D. DEFINE cte_name SELECT * FROM table_name;

Solution

1. Step 1: Recall CTE syntax

   CTEs start with the keyword WITH, followed by the CTE name, AS, and a query in parentheses.

2. Step 2: Check each option

   WITH cte_name AS (SELECT * FROM table_name) SELECT * FROM cte_name; matches the correct syntax. CREATE VIEW cte_name AS SELECT * FROM table_name; is for views, C is a subquery, and D is invalid syntax.

3. Final Answer:

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

4. Quick Check:

   CTE syntax starts with WITH [OK]

Hint: CTE always starts with WITH keyword [OK]

Common Mistakes:

• Using CREATE VIEW syntax for CTE
• Confusing subquery parentheses with CTE
• Using undefined keywords like DEFINE

3. Given the following SQL code, what will be the output?

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

medium

A. Empty result set because SalesCTE is not materialized

B. All products with total quantity sold less than 100

C. Syntax error due to missing GROUP BY

D. All products with total quantity sold greater than 100

Solution

1. Step 1: Analyze the CTE query

   The CTE calculates total quantity sold per product by grouping Sales data by ProductID and summing Quantity.

2. Step 2: Understand the main query filter

   The main query selects rows from SalesCTE where TotalSold is greater than 100, so it returns products with sales over 100 units.

3. Final Answer:

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

4. Quick Check:

   CTE filters totals > 100 = correct output [OK]

Hint: CTE output filters rows after aggregation [OK]

Common Mistakes:

• Thinking CTE is not executed or materialized
• Confusing GROUP BY usage
• Assuming syntax error without checking carefully

4. You wrote this SQL query but it throws an error:

SELECT * FROM (
  SELECT id, name FROM users
) AS user_sub WHERE user_sub.age > 30;

What is the most likely cause of the error?

medium

A. The subquery alias 'user_sub' is missing

B. The WHERE clause should be inside the subquery

C. The subquery does not include the 'age' column needed in WHERE

D. Subqueries cannot be used in FROM clause

Solution

1. Step 1: Check subquery columns

   The subquery selects only 'id' and 'name' columns, but the outer query filters on 'age' which is not selected.

2. Step 2: Understand SQL column visibility

   Since 'age' is not in the subquery result, the outer WHERE clause referencing 'age' causes an error.

3. Final Answer:

   The subquery does not include the 'age' column needed in WHERE -> Option C

4. Quick Check:

   Missing column in subquery causes error [OK]

Hint: Ensure subquery selects all columns used outside [OK]

Common Mistakes:

• Forgetting to alias subquery
• Thinking subqueries can't be in FROM
• Moving WHERE inside subquery unnecessarily

5. You want to reuse a complex query frequently in different reports without rewriting it each time. Which SQL feature should you use and why?

hard

A. Create a View because it saves the query in the database for easy reuse.

B. Use a CTE because it is permanent and can be called anytime.

C. Use a subquery because it is stored and optimized by the database.

D. Create an index because it stores the query results for reuse.

Solution

1. Step 1: Understand the need for reuse

   Reusing a complex query multiple times means you want a saved, reusable object in the database.

2. Step 2: Compare features for reuse

   Views are saved queries stored in the database and can be used repeatedly in different reports. CTEs are temporary and exist only during query execution. Subqueries are inline and not stored. Indexes improve performance but do not store queries.

3. Final Answer:

   Create a View because it saves the query in the database for easy reuse. -> Option A

4. Quick Check:

   Persistent reusable query = View [OK]

Hint: Persistent reusable query? Use a View [OK]

Common Mistakes:

• Confusing CTEs as permanent
• Thinking subqueries are stored
• Misunderstanding indexes as query storage