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.