Common Table Expressions (CTEs)
Definition: A Common Table Expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs make complex queries easier to read and maintain by breaking them down into simpler, more manageable components.
Basic Syntax:
WITH cte_name AS (
-- Your query here
)
SELECT * FROM cte_name;
Example: Let's say you have a employees table and you want to get the average salary for each department. You can use a CTE to simplify the query.
WITH DepartmentAverage AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT * FROM DepartmentAverage;
Recursive Common Table Expressions
Definition: A recursive CTE is a type of CTE that references itself in order to produce hierarchical or sequential data. Recursive CTEs are particularly useful for querying tree-structured data, such as organizational charts or folder hierarchies.
Basic Syntax:
WITH RECURSIVE cte_name AS (
-- Anchor member (base case)
SELECT initial_query
UNION ALL
-- Recursive member
SELECT recursive_query
FROM cte_name
WHERE condition
)
SELECT * FROM cte_name;
Example: Assume you have an employees table where each employee has a manager, and you want to find all employees under a specific manager.
WITH RECURSIVE EmployeeHierarchy AS (
-- Anchor member: start with a specific employee
SELECT employee_id, first_name, manager_id
FROM employees
WHERE employee_id = 1 -- Start with employee with ID 1
UNION ALL
-- Recursive member: find employees under that manager
SELECT e.employee_id, e.first_name, e.manager_id
FROM employees e
INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;
Summary
CTE: A temporary result set that simplifies complex queries by allowing you to break them into smaller, manageable pieces. It can be used for both simple and complex queries.
Recursive CTE: A specialized CTE that references itself, used for retrieving hierarchical or sequential data. It's particularly helpful when dealing with parent-child relationships in data structures.
Top comments (0)