CTE vs subquery vs view decision in SQL - Trade-offs & Expert Analysis

Overview - CTE vs subquery vs view decision

What is it?

CTEs, subqueries, and views are ways to organize and reuse parts of SQL queries. A subquery is a query inside another query, used temporarily. A CTE (Common Table Expression) is a named temporary result set that you can reference within a query. A view is a saved query stored in the database that acts like a virtual table.

Why it matters

These tools help make complex queries easier to read, maintain, and reuse. Without them, SQL queries would be long, repetitive, and hard to understand. Choosing the right one improves performance and clarity, saving time and reducing errors in real projects.

Where it fits

Before learning this, you should understand basic SQL SELECT queries and joins. After this, you can learn about query optimization, indexing, and advanced database design.

Mental Model

Core Idea

CTEs, subqueries, and views are different ways to organize and reuse query parts, balancing readability, reusability, and performance.

Think of it like...

Think of cooking: a subquery is like chopping ingredients right before cooking, a CTE is prepping a bowl of mixed ingredients to use in the recipe, and a view is like a pre-made sauce stored in the fridge for many meals.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│   Subquery   │──────▶│    CTE        │──────▶│     View      │
│ (inline use) │       │(named temp)   │       │(stored query) │
└───────────────┘       └───────────────┘       └───────────────┘

Usage:                Temporary, readable      Persistent, reusable
Scope:                Single query            Multiple queries
Performance impact:   Depends on optimizer    Depends on view design

Build-Up - 7 Steps

1

Foundation

Understanding Subqueries Basics

Concept: Introduce subqueries as queries inside other queries used to filter or calculate values.

A subquery is a SELECT statement inside another SQL statement. It runs first and provides a result for the outer query. For example, to find employees who earn more than the average salary: SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

Result

Returns names of employees earning more than the average salary.

Knowing subqueries lets you embed logic directly where needed, making queries flexible but sometimes harder to read.

2

Foundation

Introducing Views as Stored Queries

Concept: Explain views as saved queries that act like virtual tables accessible by name.

A view is created once and stored in the database. It looks like a table but is actually a saved SELECT query. For example: CREATE VIEW high_earners AS SELECT name, salary FROM employees WHERE salary > 50000; You can then query the view: SELECT * FROM high_earners;

Result

Returns all employees earning more than 50,000, using the saved view.

Views simplify repeated query use and hide complexity, improving maintainability and security.

3

Intermediate

Using CTEs for Readable Temporary Results

Concept: CTEs let you name a temporary result set inside a query for clarity and reuse within that query.

A CTE is defined with WITH and used in the following query. Example: WITH avg_salary AS (SELECT AVG(salary) AS avg_sal FROM employees) SELECT name FROM employees, avg_salary WHERE salary > avg_sal; This separates calculation from filtering, making the query easier to read.

Result

Returns employees earning more than the average salary, using a named temporary result.

CTEs improve query readability and organization without storing anything permanently.

4

Intermediate

Comparing Subqueries and CTEs

🤔Before reading on: Do you think CTEs always perform better than subqueries? Commit to your answer.

Concept: Explore differences in readability, reusability, and performance between subqueries and CTEs.

Subqueries are inline and sometimes harder to read when nested deeply. CTEs name parts of queries, making them clearer. However, performance depends on the database optimizer; sometimes subqueries and CTEs perform similarly. CTEs can be referenced multiple times in one query, avoiding repetition.

Result

Learners understand when to prefer CTEs for clarity and reuse, but not always for speed.

Understanding that CTEs are mainly for readability and reuse helps avoid assuming they always improve performance.

5

Intermediate

When to Use Views vs CTEs

🤔Before reading on: Do you think views can be used inside other views? Commit to your answer.

Concept: Clarify the difference between persistent views and temporary CTEs, and their use cases.

Views are stored in the database and can be used by many queries over time. They help reuse logic and enforce security. CTEs exist only during one query execution. Views can be nested (views using other views). Use views when you want reusable, consistent query parts; use CTEs for temporary, readable query steps.

Result

Learners see views as reusable building blocks and CTEs as temporary helpers.

Knowing the scope and lifetime difference between views and CTEs guides better design decisions.

6

Advanced

Performance Implications of Each Option

🤔Before reading on: Do you think views always improve query speed? Commit to your answer.

Concept: Discuss how database engines optimize subqueries, CTEs, and views differently and their impact on performance.

Subqueries may be optimized inline or executed multiple times. CTEs are often treated as inline views but some databases materialize them, affecting speed. Views are expanded into queries at runtime; complex views can slow queries. Indexed or materialized views improve speed but add maintenance. Understanding your database's optimizer behavior is key.

Result

Learners grasp that performance depends on database engine and query design, not just syntax choice.

Knowing optimizer behavior prevents wrong assumptions about performance and helps write efficient queries.

7

Expert

Advanced Use Cases and Pitfalls

🤔Before reading on: Can recursive queries be done with subqueries? Commit to your answer.

Concept: Explore advanced features like recursive CTEs, view limitations, and common mistakes in choosing between these options.

Recursive CTEs allow queries that refer to themselves, useful for hierarchical data. Subqueries cannot do recursion. Views cannot have parameters, limiting flexibility. Overusing views can cause complex nested queries hard to debug. Some databases materialize CTEs, causing unexpected performance hits. Choose based on query complexity, reuse needs, and performance testing.

Result

Learners understand advanced capabilities and risks, enabling smarter query design.

Recognizing advanced features and limitations helps avoid common traps and leverage powerful SQL capabilities.

Under the Hood

Subqueries are executed as part of the outer query, sometimes multiple times. CTEs are either inlined or materialized by the database engine depending on the system and query. Views are stored query definitions expanded at runtime. Some databases cache or index views (materialized views) for speed. The optimizer decides how to combine and execute these parts for best performance.

Why designed this way?

These constructs evolved to balance query complexity, readability, and performance. Subqueries provide inline flexibility. CTEs improve readability and reuse within a query. Views enable persistent reuse and security. Materialized views address performance by storing results. Tradeoffs exist between temporary and persistent, inline and stored, flexible and reusable.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│   Subquery   │──────▶│    CTE        │──────▶│     View      │
│ (inline exec)│       │(inline or mat.)│       │(stored query) │
└──────┬────────┘       └──────┬────────┘       └──────┬────────┘
       │                       │                       │
       ▼                       ▼                       ▼
┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ Query Engine  │──────▶│ Query Engine  │──────▶│ Query Engine  │
│ Optimizer &   │       │ Optimizer &   │       │ Optimizer &   │
│ Executor     │       │ Executor     │       │ Executor     │
└───────────────┘       └───────────────┘       └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think CTEs always improve query performance? Commit to yes or no.

Common Belief: CTEs always make queries faster because they are named and reusable.

Reality: CTEs can be materialized or inlined; materialization can slow queries if large. Performance depends on the database engine and query.

Why it matters: Assuming CTEs always speed up queries can lead to unexpected slowdowns in production.

Quick: Can views store data like tables? Commit yes or no.

Common Belief: Views are like tables and store data physically.

Reality: Views are virtual and do not store data unless materialized. They run their underlying query each time accessed.

Why it matters: Expecting views to improve speed without materialization can cause performance issues.

Quick: Can subqueries be recursive? Commit yes or no.

Common Belief: Subqueries can call themselves to handle hierarchical data.

Reality: Only recursive CTEs support recursion; subqueries cannot be recursive.

Why it matters: Trying to use subqueries for recursion leads to errors or inefficient workarounds.

Quick: Do you think views can have parameters like functions? Commit yes or no.

Common Belief: Views can accept parameters to customize results.

Reality: Views cannot take parameters; you must filter after selecting from a view or use table-valued functions instead.

Why it matters: Expecting parameterized views can cause design confusion and limit query flexibility.

Expert Zone

1

Some databases materialize CTEs by default, causing unexpected performance hits; knowing your DBMS behavior is crucial.

2

Views can hide complex joins and filters, but over-nesting views can make debugging and optimization very hard.

3

Recursive CTEs enable elegant hierarchical queries that are impossible with subqueries or views alone.

When NOT to use

Avoid views when you need parameterized queries; use table-valued functions instead. Avoid CTEs for very large datasets if your database materializes them, as this can hurt performance. Subqueries are less readable for complex logic; prefer CTEs or views for clarity.

Production Patterns

Use views to enforce security by restricting columns or rows. Use CTEs to break down complex queries into readable parts. Use recursive CTEs for organizational charts or bill-of-materials queries. Test performance with EXPLAIN plans to choose between subqueries, CTEs, or views.

Connections

Functional Programming

Similar pattern of naming and reusing expressions (like functions) to improve clarity and reuse.

Understanding how CTEs name query parts is like defining functions, helping grasp modular code design.

Caching in Web Development

Views and materialized views act like cached data to speed up repeated access.

Knowing caching principles clarifies why materialized views improve speed but need refreshing.

Recipe Preparation in Cooking

Organizing query parts is like prepping ingredients or sauces to simplify cooking steps.

This cross-domain view helps appreciate why temporary and persistent preparations matter.

Common Pitfalls

#1 Using a subquery repeatedly instead of a CTE for the same calculation.

Wrong approach:

SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) AND department_id IN (SELECT department_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees));

Correct approach:

WITH avg_salary AS (SELECT AVG(salary) AS avg_sal FROM employees)
SELECT name FROM employees WHERE salary > (SELECT avg_sal FROM avg_salary) AND department_id IN (SELECT department_id FROM employees WHERE salary > (SELECT avg_sal FROM avg_salary));

Root cause:

Not realizing that repeating the same subquery causes redundant calculations and hurts performance.

#2 Expecting a view to improve query speed without materialization.

Wrong approach:

CREATE VIEW expensive_view AS SELECT * FROM large_table JOIN another_large_table ON ...;
SELECT * FROM expensive_view WHERE condition = 'value';

Correct approach:

CREATE MATERIALIZED VIEW expensive_view AS SELECT * FROM large_table JOIN another_large_table ON ...;
-- Refresh materialized view as needed
SELECT * FROM expensive_view WHERE condition = 'value';

Root cause:

Misunderstanding that views are virtual and do not store data, so complex views can be slow.

#3 Trying to use a subquery for recursive data retrieval.

Wrong approach:

SELECT employee_id, manager_id FROM employees WHERE manager_id IN (SELECT employee_id FROM employees WHERE manager_id IN (SELECT employee_id FROM employees ...));

Correct approach:

WITH RECURSIVE employee_hierarchy AS (
SELECT employee_id, manager_id FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id FROM employees e JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

Root cause:

Not knowing that recursion requires recursive CTEs, not nested subqueries.

Key Takeaways

Subqueries, CTEs, and views are tools to organize SQL queries with different scopes and lifetimes.

CTEs improve readability and reuse within a single query but may or may not improve performance.

Views store reusable query logic persistently but do not store data unless materialized.

Choosing between them depends on query complexity, reuse needs, and database optimizer behavior.

Advanced features like recursive CTEs enable powerful queries that subqueries and views cannot handle.