SQL Common Table Expression: Syntax, Usage, and Examples
A SQL common table expression (CTE) is a temporary result set within a query that improves readability and modularity. Common table expressions SQL can be used in complex queries, recursion, and aggregations.
How to Use a Common Table Expression in SQL
The basic syntax for a common table expression in SQL follows this structure:
sql
WITH cte_name AS (
SELECT column1, column2
FROM some_table
WHERE some_condition
)
SELECT * FROM cte_name;
WITHintroduces the common table expression.cte_nameis the alias for the temporary result set.- The enclosed
SELECTstatement retrieves data. - The final query references the CTE instead of using subqueries.
Example: Using a CTE in SQL
sql
WITH HighValueCustomers AS (
SELECT customer_id, COUNT(order_id) AS order_count
FROM Orders
GROUP BY customer_id
HAVING COUNT(order_id) > 5
)
SELECT c.first_name, c.last_name, h.order_count
FROM Customers c
JOIN HighValueCustomers h ON c.customer_id = h.customer_id;
This query first retrieves customers who placed more than five orders using the CTE and then joins that data with the Customers table.
When to Use Common Table Expressions in SQL
Improving Query Readability
Long and complex queries benefit from CTEs by breaking them into smaller, named sections, making them easier to understand and debug.
Recursive Queries
A recursive common table expression in SQL allows hierarchical data retrieval, such as an employee-manager hierarchy.
sql
WITH EmployeeHierarchy AS (
SELECT employee_id, manager_id, first_name, last_name
FROM Employees
WHERE manager_id IS NULL -- Start with the top-level manager
UNION ALL
SELECT e.employee_id, e.manager_id, e.first_name, e.last_name
FROM Employees e
JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;
This query recursively fetches all employees under a specific manager.
Reusable Queries in SQL
A common table expression SQL query can be referenced multiple times within a statement without duplicating code.
sql
WITH OrderCounts AS (
SELECT customer_id, COUNT(order_id) AS order_count
FROM Orders
GROUP BY customer_id
)
SELECT c.first_name, c.last_name, oc.order_count
FROM Customers c
JOIN OrderCounts oc ON c.customer_id = oc.customer_id
WHERE oc.order_count > 3;
CTEs are especially useful in back-end development for organizing complex queries and improving database efficiency. If you want to learn more about using SQL in real-world applications, check out our Back-End Development course.
Examples of Common Table Expressions SQL
Filtering Aggregated Data
Using a CTE simplifies filtering aggregated values.
sql
WITH ProductSales AS (
SELECT product_id, SUM(quantity) AS total_sold
FROM OrderDetails
GROUP BY product_id
)
SELECT p.product_name, ps.total_sold
FROM Products p
JOIN ProductSales ps ON p.product_id = ps.product_id
WHERE ps.total_sold > 500;
Recursive CTE for Parent-Child Relationships
This example retrieves hierarchical data, such as company departments.
sql
WITH Recursive DepartmentHierarchy AS (
SELECT department_id, department_name, parent_department_id
FROM Departments
WHERE parent_department_id IS NULL -- Top-level department
UNION ALL
SELECT d.department_id, d.department_name, d.parent_department_id
FROM Departments d
JOIN DepartmentHierarchy dh ON d.parent_department_id = dh.department_id
)
SELECT * FROM DepartmentHierarchy;
Using Multiple CTEs in One Query
Multiple CTEs can be defined in a single query by separating them with commas.
sql
WITH
CustomersWithOrders AS (
SELECT customer_id, COUNT(order_id) AS order_count
FROM Orders
GROUP BY customer_id
),
HighValueCustomers AS (
SELECT customer_id
FROM CustomersWithOrders
WHERE order_count > 10
)
SELECT c.first_name, c.last_name
FROM Customers c
JOIN HighValueCustomers hvc ON c.customer_id = hvc.customer_id;
Learn More About Common Table Expressions in SQL
CTEs vs. Subqueries
CTEs and subqueries both allow temporary result sets, but CTEs offer:
- Better readability by naming result sets.
- The ability to reference the same dataset multiple times.
- Easier debugging and step-by-step execution.
For example, a nested subquery:
sql
SELECT first_name, last_name, total_sold
FROM (
SELECT p.product_id, p.product_name, SUM(od.quantity) AS total_sold
FROM Products p
JOIN OrderDetails od ON p.product_id = od.product_id
GROUP BY p.product_id, p.product_name
) AS ProductSales
WHERE total_sold > 500;
Using a CTE:
sql
WITH ProductSales AS (
SELECT p.product_id, p.product_name, SUM(od.quantity) AS total_sold
FROM Products p
JOIN OrderDetails od ON p.product_id = od.product_id
GROUP BY p.product_id, p.product_name
)
SELECT first_name, last_name, total_sold
FROM ProductSales
WHERE total_sold > 500;
When to Use CTEs vs. Temporary Tables
CTEs:
- Exist only within the query.
- Do not persist beyond execution.
- Best for improving readability and recursion.
Temporary tables:
- Persist for the duration of a session.
- Can be indexed for better performance on large datasets.
- Useful when needing intermediate results across multiple queries.
Example of a temporary table:
sql
CREATE TEMP TABLE HighValueOrders AS
SELECT order_id, customer_id, total_amount
FROM Orders
WHERE total_amount > 1000;
How to Use Common Table Expressions in SQL for Updates and Deletes
Updating Data Using a CTE
CTEs can be used in UPDATE statements to modify data.
sql
WITH DiscountedProducts AS (
SELECT product_id, price
FROM Products
WHERE price > 50
)
UPDATE Products
SET price = price * 0.9
WHERE product_id IN (SELECT product_id FROM DiscountedProducts);
Deleting Data Using a CTE
CTEs also work with DELETE statements.
sql
WITH OldOrders AS (
SELECT order_id
FROM Orders
WHERE order_date < '2020-01-01'
)
DELETE FROM Orders
WHERE order_id IN (SELECT order_id FROM OldOrders);
Common table expressions in SQL provide a structured way to simplify queries, handle recursion, and improve readability. They are particularly useful for hierarchical data, aggregated filters, and temporary data sets within a query.