Common Table Expressions in SQL

Hello Folks.

Photo by Jonah Brown on Unsplash

1. Basic CTE Syntax

WITH EmployeeCTE AS (
    SELECT employee_id, first_name, last_name, department_id
    FROM employees
)
SELECT * FROM EmployeeCTE;

2. CTE with Joins

WITH DepartmentCTE AS (
    SELECT department_id, department_name
    FROM departments
),
EmployeeCTE AS (
    SELECT employee_id, first_name, last_name, department_id
    FROM employees
)
SELECT e.employee_id, e.first_name, d.department_name
FROM EmployeeCTE e
JOIN DepartmentCTE d ON e.department_id = d.department_id;

3. Recursive CTE

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT employee_id, manager_id, first_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
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

Photo by Kelly Sikkema on Unsplash

4. CTE for Aggregationssql

WITH DepartmentSalaries AS (
    SELECT department_id, SUM(salary) AS total_salary
    FROM…