SQL Common Table Expression

What is a Common Table Expression (CTE) in SQL?

A Common Table Expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are defined using the WITH clause and provide a way to simplify complex queries by breaking them down into more manageable parts. They improve readability and maintainability by allowing you to structure your SQL code in a more logical manner.

Syntax of a CTE

The syntax for defining a CTE is as follows:

WITH cte_name (column1, column2, ...)
AS
(
    -- CTE Query
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
-- Main Query
SELECT column1, column2, ...
FROM cte_name
WHERE condition;

When to Use a CTE

CTEs are particularly useful in the following scenarios:

1. Simplifying Complex Queries: When you need to break down a complex query into smaller, more manageable parts.

2. Recursive Queries: When you need to perform recursive operations, such as hierarchical data retrieval (e.g., organizational charts or folder structures).

3. Improving Readability: When you want to make your SQL code more readable and maintainable by separating different logical parts of the query.

4. Reusing Result Sets: When you need to reuse a result set multiple times within a single query.

SQL Schema

We'll create two tables: employees and departments. The employees table will store information about employees, including their ID, name, salary, department ID, and manager ID. The departments table will store information about departments.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    salary DECIMAL(10, 2),
    department_id INT,
    manager_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id),
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

Sample Data

Let's insert some sample data into these tables to demonstrate how to use CTEs.

-- Insert data into departments
INSERT INTO departments (department_id, department_name) VALUES
(1, 'Human Resources'),
(2, 'Finance'),
(3, 'Engineering');

-- Insert data into employees
INSERT INTO employees (employee_id, employee_name, salary, department_id, manager_id) VALUES
(1, 'Alice', 80000, 1, NULL),
(2, 'Bob', 90000, 2, NULL),
(3, 'Charlie', 75000, 2, 2),
(4, 'David', 70000, 3, NULL),
(5, 'Eve', 95000, 3, 4),
(6, 'Frank', 85000, 3, 4),
(7, 'Grace', 90000, 2, 2);

How to Use a CTE

Let's look at some examples to understand how to use CTEs in different scenarios:

1. Simplifying a Complex Query

Find the employees who earn more than the average salary in their department.

WITH avg_salaries AS
(
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.employee_id, e.employee_name, e.salary, a.avg_salary
FROM employees e
JOIN avg_salaries a
ON e.department_id = a.department_id
WHERE e.salary > a.avg_salary;

2. Recursive Query

Retrieve all employees and their managers in a hierarchical structure.

WITH employee_hierarchy AS
(
    SELECT employee_id, employee_name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.employee_name, e.manager_id
    FROM employees e
    JOIN employee_hierarchy eh
    ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

Github:

https://github.com/vipinputhanveetil/sql-concepts/blob/main/sql_cte.sql

Conclusion

Common Table Expressions (CTEs) are a powerful feature in SQL that allow you to simplify and organize your queries, making them more readable and maintainable. By understanding how and when to use CTEs, you can enhance the efficiency and clarity of your SQL code.