What’s New ?

The Top 10 favtutor Features You Might Have Overlooked

Read More

CTE vs Subquery vs Temp Table in SQL (with Examples)

  • Feb 01, 2024
  • 7 Minutes Read
  • Why Trust Us
    We uphold a strict editorial policy that emphasizes factual accuracy, relevance, and impartiality. Our content is crafted by top technical writers with deep knowledge in the fields of computer science and data science, ensuring each piece is meticulously reviewed by a team of seasoned editors to guarantee compliance with the highest standards in educational content creation and publishing.
  • By Tanish Mallik
CTE vs Subquery vs Temp Table in SQL (with Examples)

In SQL, handling tables efficiently is key for good data management. Developers often use different methods like CTEs, temporary tables, and subqueries. In this article, we will compare and find the differences between CTE vs Subquery in SQL.

CTEs make code easier to read and organize by creating temporary named result sets. Temporary tables are like quick storage solutions during a session, handy for short tasks or storing results. Subqueries are nested queries that work well for quick operations within a single query. 

Difference Between CTE and Subquery in SQL

CTE in SQL stands for Common Table Expression, which is a named temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement in SQL. Common Table Expressions (CTEs) only stick around for the specific query, making your code clearer and more organized. 

CTEs are great when you want to break down complex tasks into simpler manageable parts and reuse them as needed. They make writing SQL code easier to understand and manage, especially when dealing with more intricate database operations.

On the flip side, a subquery is a quick, one-time query inserted inside another. Subqueries are handy for doing short, specific tasks within a larger query. So, choosing between CTEs and subqueries narrows down to how complicated your query is – use CTEs for readability in big queries, and subqueries for quick tasks in a single query.

Using CTE

CTE starts with the WITH keyword, where you give the CTE a name and define what you want in it. Then, you use this named CTE in your main query to retrieve and process data. 


WITH CTE_Name (column1, column2, ...) AS (


Let us now understand this with an example.

Table: Sales

Using CTE Subquery Sales Table

The SQL code below uses a CTE named HighSalesCTE to filter the Sales table, selecting products with quantities sold more than 100. The query then retrieves all columns from this CTE, enhancing code.

SQL Query:

WITH HighSalesCTE AS (
SELECT ProductID, ProductName, QuantitySold
FROM Sales
WHERE QuantitySold > 100



Using CTE Subquery Sales Table Output

Using Subquery

A subquery is like an inserted query inside a bigger query. First, we set up our main query to select data from a table. Then, in the WHERE clause or another relevant part, we include a subquery to add extra conditions or get specific information. 


SELECT Column1, Column2
FROM Table
WHERE Condition;


We will use the same example to compare the subquery and CTE.

Table: Sales

Using CTE Subquery Sales Table

The SQL query below selects the columns ProductID, ProductName, and QuantitySold from the Sales table where the quantity sold is greater than 100.
SQL Query:

SELECT ProductID, ProductName, QuantitySold
FROM Sales
WHERE QuantitySold > 100;



Using CTE Subquery Sales Table Output

Using Temp Table

In SQL, think of temporary tables as a quick storage solution for short tasks. Unlike regular tables, these are just here for a little while, making it easier to handle big challenges by breaking them into smaller steps. They come in handy when you need to save and use results from a quick task during your work session.


-- Create Temporary Table
SELECT Column1, Column2
FROM Table
WHERE Condition;

-- Query using Temporary Table
SELECT t.Column1, t.Column2.
FROM AnotherTable a
JOIN Temp_Table_Name t ON a.Join_Column = t.Join_Column;

-- Drop Temporary Table


We will use the same example of the Sales table.

The SQL Query finds low-sales products by creating a temporary table, shows relevant details through a query, and then cleans up. It's a quick way to analyze and report on below-average product sales without changing the permanent database.

SQL Query:

SELECT ProductID, SUM(Quantity) AS TotalSales
FROM Sales
HAVING SUM(Quantity) <120

SELECT p.ProductID, p.ProductName, t.TotalSales
FROM Products p
JOIN TempLowSales t ON p.ProductID = t.ProductID;




Using Temp Table Output

Difference in Performances of CTE and Subquery

Let us look at some of the key differences between CTE and Subquery in SQL:




Execution Plan

Acts like a helper table, often optimized

Treated separately, might have its plan


Makes code cleaner by breaking it down

Can make code a bit hard to read


Can be used multiple times in one query

Usually used once, not reusable


Good for big and complex tasks

May slow down with big or complex data

Code Structure

Keep things tidy and organized

A nested structure may make code less clean

Use Cases

Best for complex tasks with many uses

Good for quick, one-time jobs in a query


Each method has its strengths, with CTEs focusing on readability, subqueries handling quick tasks, and temporary tables providing a more lasting storage solution.

Is CTE faster than Subquery?

In SQL, whether a CTE or a subquery is faster depends on factors like query complexity and database optimizations. CTEs are best for enhancing the readability and reusability of intermediate results, subqueries can be perfect for simpler operations. The performance difference can be influenced by database configuration and the specific query at hand. 

What are the disadvantages of CTE?

CTE has a limited scope as they're only available within the query where they're defined. This is a major limitation because, in some database systems, they may also impact performance due to repeated computation. Note that all database management systems support CTEs which is crucial because it limits its portability. 


In SQL, we have three ways to deal with data: CTEs, subqueries, and temporary tables

  • CTEs are like mini-tables inside a query that make complicated code easier to understand. They're great for big queries to keep things neat.
  • Subqueries are quick, one-time queries nested within another. They work well for specific, dynamic tasks but might get a bit difficult in complex situations.
  • Temporary tables are like short-term storage solutions for your data during a session. They're useful when dealing with more extensive analyses or if you need to refer to results from a subquery multiple times. 

Choose CTEs for cleaner and more readable code in larger queries, subqueries for quick, specific tasks, and temporary tables when you need to store and reference interim results during a session. Each method has its strengths, offering flexibility in handling data in SQL. In this article, we discussed all of them and their comparison based on performances.

FavTutor - 24x7 Live Coding Help from Expert Tutors!

About The Author
Tanish Mallik
I am Tanish, a final-year computer science student with extensive expertise in SQL. My background in the field of data science has honed my strong analytical skills, and I also have hands-on experience in developing data integration and optimization pipelines using Python and MSBI. I am eager to contribute innovative ideas and make an impact in the ever-evolving world of technology.