CTE vs subquery vs view decision in SQL - Performance Comparison

Time Complexity: CTE vs subquery vs view decision

O(n)

Understanding Time Complexity

When choosing between CTEs, subqueries, and views, it's important to understand how each affects the time it takes for a database to run a query.

We want to know how the work grows as the data gets bigger for each option.

Scenario Under Consideration

Analyze the time complexity of these three ways to organize a query:

-- Using a CTE
WITH RecentOrders AS (
  SELECT * FROM Orders WHERE OrderDate > CURRENT_DATE - INTERVAL '30 days'
)
SELECT CustomerID, COUNT(*) FROM RecentOrders GROUP BY CustomerID;

-- Using a subquery
SELECT CustomerID, COUNT(*) FROM (
  SELECT * FROM Orders WHERE OrderDate > CURRENT_DATE - INTERVAL '30 days'
) AS RecentOrders GROUP BY CustomerID;

-- Using a view
-- Assume view RecentOrdersView is defined as:
-- SELECT * FROM Orders WHERE OrderDate > CURRENT_DATE - INTERVAL '30 days'
SELECT CustomerID, COUNT(*) FROM RecentOrdersView GROUP BY CustomerID;

Each method filters recent orders and counts them per customer.

Identify Repeating Operations

Look at what repeats or loops over data:

• Primary operation: Scanning the Orders table to find recent orders.
• How many times: Once per query execution, regardless of method.

How Execution Grows With Input

As the number of orders grows, the database must check more rows to find recent ones.

Input Size (n)	Approx. Operations
10	About 10 row checks
100	About 100 row checks
1000	About 1000 row checks

Pattern observation: The work grows roughly in direct proportion to the number of rows in Orders.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the query grows linearly with the number of rows in the Orders table.

Common Mistake

[X] Wrong: "Using a view is always faster than a subquery or CTE because it is precomputed."

[OK] Correct: Views are just saved query definitions and usually run fresh each time, so they don't reduce the amount of work by themselves.

Interview Connect

Understanding how these query structures affect performance helps you explain your choices clearly and shows you know how databases handle work as data grows.

Self-Check

"What if the CTE is referenced multiple times in the query? How would that affect the time complexity compared to a subquery or view?"