CTEs in SQL: Common Table Expressions Explained Simply

“What is a CTE in SQL and Why is it used?” This question often puzzles budding programmers, and understanding it can untangle complex queries, boost readability, and streamline SQL processes. By learning “What is a CTE in SQL and Why is it used?” you’ll discover solutions to repetitive subqueries and more readable code. Keep reading!

What is a CTE in SQL and why is it used?

A Common Table Expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.
It improves query readability and makes complex SQL easier to manage.

Example:

WITH EmployeeCTE AS (
    SELECT EmployeeID, FirstName, LastName, Department
    FROM Employees
    WHERE Department = 'Sales'
)
SELECT * FROM EmployeeCTE;

Here, EmployeeCTE acts like a temporary table only for this query.

How do you write a CTE in SQL?

The basic syntax is:

WITH CTE_Name (column1, column2, …) AS (
    SELECT columns
    FROM table
    WHERE condition
)
SELECT * FROM CTE_Name;

What is the difference between a CTE and a subquery?

CTE: Improves readability, can be recursive, and can be referenced multiple times in the same query.

Subquery: Embedded directly inside another query, often harder to read, and cannot reference itself.

Example – Subquery vs CTE:

-- Using Subquery
SELECT * 
FROM (SELECT EmployeeID, Department FROM Employees WHERE Department='IT') AS SubQ;

-- Using CTE
WITH IT_Employees AS (
    SELECT EmployeeID, Department FROM Employees WHERE Department='IT'
)
SELECT * FROM IT_Employees;

👉 Both give the same result, but the CTE version is cleaner and reusable.

Sample code of CTEs in SQL

sql
WITH Sales_CTE AS (
    SELECT SalesPersonID, SUM(TotalDue) AS TotalSales
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT e.BusinessEntityID, e.FirstName, e.LastName, s.TotalSales
FROM Sales_CTE s
JOIN Sales.SalesPerson sp ON s.SalesPersonID = sp.BusinessEntityID
JOIN Person.Person e ON sp.BusinessEntityID = e.BusinessEntityID
WHERE s.TotalSales > 5000
ORDER BY s.TotalSales DESC;

Explanation of the Code

The provided SQL code is structured using a Common Table Expression (CTE), named `Sales_CTE`. This CTE enables us to simplify complex queries, making them more readable and easier to manage. Let’s break it down step-by-step:

The CTE `Sales_CTE` begins by selecting the `SalesPersonID` and using the `SUM()` function to calculate the total sales (`TotalSales`) for each sales rep. It does this by grouping the data by `SalesPersonID` from the `Sales.SalesOrderHeader` table.

The main query then starts by selecting data, pulling `BusinessEntityID`, `FirstName`, `LastName`, and the calculated `TotalSales` from the CTE and other related tables.

A `JOIN` is used to connect the CTE with `Sales.SalesPerson` and `Person.Person` tables, linking them based on matching `BusinessEntityID` columns.

The `WHERE` clause filters results to show only those salespeople with `TotalSales` exceeding £5000.

Finally, the results are ordered in descending order by `TotalSales`, displaying the highest sellers first.

Output

Understanding Real-Life Uses of CTE in SQL

Data Simplification at Amazon: Amazon uses CTEs to simplify complex queries for their sales reports. By summarizing data before joining tables, they efficiently handle massive datasets without overloading systems.
```
WITH SalesSummary AS (
    SELECT CustomerID, SUM(Sales) AS TotalSales
    FROM SalesData
    GROUP BY CustomerID
)
SELECT *
FROM SalesSummary
WHERE TotalSales > 10000;
    
```
The output shows customers with sales over £10,000, helping Amazon target high-value clients effectively.
Trend Analysis at Netflix: Netflix employs CTEs to track content consumption trends by constructing queries in layers. These insights are crucial for deciding which series to renew or cancel.
```
WITH MonthlyViews AS (
    SELECT ContentID, MONTH(ViewDate) AS MonthViewed, COUNT(*) AS ViewCount
    FROM ViewingData
    GROUP BY ContentID, MONTH(ViewDate)
)
SELECT ContentID, AVG(ViewCount) AS AverageMonthlyViews
FROM MonthlyViews
GROUP BY ContentID;
    
```
The result helps Netflix identify consistently popular content and strategize its marketing.

Performance Metrics at Spotify: At Spotify, CTEs are used for generating performance metrics of songs and artists, allowing them to measure year-over-year growth or decline in streaming.
```
WITH ArtistPlays AS (
    SELECT ArtistID, YEAR(StreamDate) AS Year, COUNT(*) AS PlayCount
    FROM StreamData
    GROUP BY ArtistID, YEAR(StreamDate)
)
SELECT ArtistID, Year, PlayCount
FROM ArtistPlays
WHERE Year = 2023;
    
```
Outputs provide insights into artists’ performance trends over time, guiding Spotify’s promotional efforts.

Unlock the power of efficient coding with our AI-powered sql online compiler. Instantly write, run, and test your SQL code, leveraging the intelligence of AI to streamline your coding process. Enjoy seamless integration and immediate feedback, making your coding journey faster and more efficient than ever before.

Common Questions About CTE’s in SQL

Can a CTE reference itself (recursive CTE)?

Yes ✅, that’s called a recursive CTE. It’s often used for hierarchical data like org charts, family trees, or category levels.

Example – Recursive CTE for employee hierarchy:

WITH EmployeeHierarchy AS (
    -- Anchor query
    SELECT EmployeeID, ManagerID, FirstName
    FROM Employees
    WHERE ManagerID IS NULL  -- Top-level manager
    
    UNION ALL
    
    -- Recursive part
    SELECT e.EmployeeID, e.ManagerID, e.FirstName
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

👉 This finds employees reporting to managers in a hierarchy.

When should I use a CTE instead of a temporary table?

Use a CTE when:

You want readability in complex queries.
You need recursion.
You only need the temporary result within a single query.

Use a temporary table when:

You need to reuse the result multiple times across different queries.
You want to add indexes for performance.

Can I use multiple CTEs in a single SQL query?

Yes ✅, you can define multiple CTEs separated by commas.

Example – Multiple CTEs:

WITH SalesCTE AS (
    SELECT EmployeeID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY EmployeeID
),
HighPerformers AS (
    SELECT EmployeeID, TotalSales
    FROM SalesCTE
    WHERE TotalSales > 50000
)
SELECT * FROM HighPerformers;

👉 Here, SalesCTE is used inside HighPerformers.

Do CTEs improve SQL query performance?

❌ Not always.

CTEs are about readability and maintainability.
Performance depends on the query and database engine.

In some databases (like SQL Server), a CTE is just a query rewrite, not a materialized table.

Can I insert, update, or delete data using a CTE?

Yes ✅, CTEs can be used with INSERT, UPDATE, or DELETE.

Example – Update with CTE:

WITH BonusEligible AS (
    SELECT EmployeeID
    FROM Employees
    WHERE YearsAtCompany > 5
)
UPDATE Employees
SET Bonus = 1000
WHERE EmployeeID IN (SELECT EmployeeID FROM BonusEligible);

👉 This updates employees eligible for a bonus.

How do recursive CTEs work for hierarchical data?

Recursive CTEs repeat execution until a stopping condition is reached.
Useful for:

Employee/Manager hierarchies

File directory structures
Bill of materials

(See Example in Q4 above).

Can I nest a CTE inside another CTE?

Yes ✅.

WITH FirstCTE AS (
    SELECT EmployeeID, Department FROM Employees
),
SecondCTE AS (
    SELECT Department, COUNT(*) AS DeptCount
    FROM FirstCTE
    GROUP BY Department
)
SELECT * FROM SecondCTE;

What is the scope of a CTE?

A CTE exists only for the query immediately following it.
It cannot be reused across multiple queries unless redefined.

Are CTEs supported in all SQL databases?

✅ Supported: SQL Server, PostgreSQL, Oracle, SQLite, MySQL (8.0+).
❌ Not supported: MySQL versions < 8.0.

What are the limitations of CTEs?

Cannot be reused across multiple queries.

Performance is sometimes worse than indexed temp tables.
Recursive CTEs can be slow with large datasets.

Does using a CTE make SQL code more readable?

Yes ✅, that’s the biggest advantage.

Breaks down large queries.
Easier to debug.
More maintainable for teams.

Conclusion

Completing ‘What is a CTE in SQL and Why is it used?’ opens up new dimensions in handling complex queries efficiently. It’s empowering to see how a CTE streamlines your SQL work. Dive deeper into programming with Newtum and continue your enriching learning journey!

Edited and Compiled by

This article was compiled and edited by @rasikadeshpande, who has over 4 years of experience in writing. She’s passionate about helping beginners understand technical topics in a more interactive way.

About The Author

Rasika Deshpande

Rasika Deshpande, a passionate writer with over 4 years of experience in the EdTech industry. At Newtum, she writes, edit, and compile engaging blog and other content. Writing is not just a profession for her; it’s a passion that fuels creativity and dedication to the field of writing.

See author's posts