Subquery & CTE & Temporary Table in SQL In-depth comparison analysis

In-depth comparison of Subquery, CTE, and Temporary Table

All three technologies are usedCreate a temporary dataset, but they have significant differences in implementation methods, usage scenarios and performance characteristics. Let's use "Mathematics plays paper"To analyze the metaphor in depth:

1. Subquery

simile: It's like taking a walk during the problem solving processDraft calculation written next to the title

Features：

Inline: directly nested in SQL statements (SELECT/FROM/WHERE clauses, etc.)
One-time use: Only once it is defined
No name: Usually there is no explicit name (unless it is a derived table)
Scope: Only valid in queries containing it

Example：

SELECT user_id 
FROM orders 
WHERE amount &gt; (SELECT AVG(amount) FROM orders);  -- WHERESubquery in clause

Applicable scenarios：

Simple single-use calculation
As filtering conditions or comparison values
Quick test logic without reuse

2. Common Table Expression (CTE, Common Table Expression)

simile：Take out a draft paperWrite intermediate steps and you can read them at any time

Features：

Explicit naming:useWITH cte_name ASSyntax definition
Reusability: Multiple CTEs can be defined in the same WITH clause, and the subsequent CTEs can refer to the previous one
Query-level scope: Only valid in a single SQL statement immediately following it
Recursive: Support recursive query (processing hierarchical data)

Example：

-- useCTEand comparison operators &gt;，First, filter out the total consumption exceeds1000High-value users and recent30Active users in the day，Finally passed INTERSECT Take the intersection of the two，Find outA core user group that is both high-consumption and active。
WITH high_value_customers AS (
    SELECT user_id FROM orders GROUP BY user_id HAVING SUM(amount) &gt; 1000
), #Filter users with orders of more than 1,000active_customers AS (
    SELECT user_id FROM logins WHERE login_date &gt; CURRENT_DATE - 30
) #Filter active users with login records within 30 daysSELECT * FROM high_value_customers 
INTERSECT 
SELECT * FROM active_customers;

Applicable scenarios：

Modular design for complex queries
Requires multiple references to the same result set
Recursive query
Improve the readability of complex queries

3. Temporary Table

simile: Prepare one specificallynotebookRecord intermediate results, you can read and modify repeatedly

Features：

Physical storage: Actually stored intempdb(memory or disk)
Session-level scope: After creationThe entire sessionAvailable until explicit deletion or session ends
Indexable: You can add index optimization performance
Can be modified: Support INSERT/UPDATE/DELETE operation
Use across queries: Can be used by multiple queries in the same session

Example：

CREATE TEMPORARY TABLE temp_high_value AS
SELECT user_id FROM orders GROUP BY user_id HAVING SUM(amount) &gt; 1000;
ALTER TABLE temp_high_value ADD INDEX (user_id);  -- You can add indexes
SELECT * FROM temp_high_value h JOIN users u ON h.user_id = ;
DROP TEMPORARY TABLE IF EXISTS temp_high_value;  -- Explicit cleaning

Applicable scenarios：

ComplexETL Process
Intermediate results that require multiple reuses
Large dataset processing (especially when index optimization is required)
Share data across multiple SQL statements

The core comparison of the three

characteristic	Subquery	CTE	Temporary Table
Storage method	Logical existence, not physically stored	Logic exists, and may be materialized by the optimizer	Physical storage in tempdb
Scope	Current clause	Current statement	The whole session
life cycle	Query execution period	Query execution period	Explicit deletion or session end
Is it reusable	Not reusable	Reference can be made in the same WITH clause	Cross-query reuse
Is it possible to modify	Not modified	Not modified	INSERT/UPDATE/DELETE
Whether indexing is supported	Not supported	Not supported	support
Performance Features	Simple query efficient	Medium complexity query optimal	Optimal complex data processing
Syntax Complexity	Simple	medium	Higher
Typical usage scenarios	Simple filtering/calculation	Complex query modularity	Share data across statements/large processing

How to choose?

Simple calculation→ Subquery
Medium complexity query→ CTE (improve readability)
Requires multiple references/modifications→ Temporary table
Recursive query → CTE WITH RECURSIVE
Session-level reuse→ Temporary table

Remember: As SQL complexity increases, the usual development path is: subquery → CTE → temporary tables. The optimizer handles the three differently and needs to be tested and verified in performance-critical scenarios.

This is the article about the in-depth comparison and analysis of Subquery & CTE & Temporary Table in SQL. For more related contents of SQL subquery, CTE and temporary table, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!