PostgreSQL: Common Table Expression (CTE)
A Common Table Expression (CTE) in PostgreSQL, also known as a "WITH" query, allows you to create temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are particularly useful for breaking down complex queries into simpler parts, improving readability, and making maintenance easier.
Syntax:
The basic syntax of a CTE in PostgreSQL is:
WITH cte_name AS (
-- inner query
)
SELECT * FROM cte_name;
Here:
You can also chain multiple CTEs by separating them with commas:
WITH cte1 AS (
-- first query
),
cte2 AS (
-- second query
)
SELECT * FROM cte1
JOIN cte2 ON cte1.id = cte2.id;
Example 1: Basic CTE
Code:
-- Define a CTE that calculates the total sales for each customer
WITH total_sales AS (
SELECT customer_id, SUM(sales_amount) AS total_amount
FROM sales
GROUP BY customer_id
)
-- Use the CTE in the main query to retrieve customers with high sales
SELECT customer_id, total_amount
FROM total_sales
WHERE total_amount > 5000;
Explanation:
Example 2: Recursive CTE
Recursive CTEs are useful for hierarchical or tree-structured data, such as finding all employees under a manager.
Code:
-- Define a recursive CTE to find all employees under a specific manager
WITH RECURSIVE employee_hierarchy AS (
-- Anchor member: starting point of recursion
SELECT employee_id, manager_id, employee_name
FROM employees
WHERE manager_id = 1 -- Start with the top-level manager
UNION ALL
-- Recursive member: gets subordinates for each employee in the previous step
SELECT e.employee_id, e.manager_id, e.employee_name
FROM employees e
INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
-- Use the recursive CTE to get all employees in the hierarchy
SELECT * FROM employee_hierarchy;
Explanation:
Important Notes:
1. Performance:
2. Recursive CTEs:
3. Readability and Maintenance:
Summary:
Common Table Expressions (CTEs) are versatile and powerful tools in PostgreSQL for simplifying complex queries, especially with recursive structures. Using CTEs can enhance the readability and maintainability of SQL code, as well as optimize certain types of queries.
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4