Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a temporary, named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are defined using the `WITH` clause.

Key benefits of using CTEs:

• Readability: They break down complex queries into simple, logical building blocks.
• Reusability: A CTE can be referenced multiple times within the main query.
• Recursion: They can be used to write recursive queries, which are useful for hierarchical data (e.g., organization charts).

A CTE is defined with a name, an optional column list, and a query.

WITH CteName (column1, column2, ...) AS (
  -- CTE query definition
  SELECT ...
)
-- Main query
SELECT * FROM CteName;

Example:

Find all employees in the 'Engineering' department.

WITH EngineeringEmployees AS (
  SELECT id, name, salary
  FROM employees
  WHERE department = 'Engineering'
)
SELECT name, salary
FROM EngineeringEmployees
WHERE salary > 80000;

You can define multiple CTEs in a single `WITH` clause, separated by commas. Each subsequent CTE can reference the ones defined before it.

WITH
  DeptSalaries AS (
    SELECT department, SUM(salary) as total_sal
    FROM employees
    GROUP BY department
  ),
  DeptBudgets AS (
    SELECT name, budget
    FROM departments
  )
SELECT
  ds.department,
  ds.total_sal,
  db.budget
FROM DeptSalaries ds
JOIN DeptBudgets db ON ds.department = db.name;

This query first calculates total salaries per department, then joins this information with department budgets.