The WITH clause in SQL

The WITH clause in SQL, also known as a Common Table Expression (CTE), is often used to define a temporary result set that can be referred to within the main query. This is particularly useful for making complex queries more readable and reusable.

Here’s the general syntax of a query using a CTE:

WITH records AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT *
FROM records
WHERE some_condition;

Example 1: Simple Usage

WITH records AS (
        SELECT id, name, salary
        FROM employees
                WHERE salary > 50000
)
SELECT *
FROM records
WHERE name LIKE 'A%';

Example 2: Multiple CTEs

WITH high_earners AS (
        SELECT id, name, salary
        FROM employees
                WHERE salary > 70000
),
low_earners AS (
        SELECT id, name, salary
        FROM employees
                WHERE salary <= 70000
)
SELECT *
FROM high_earners
UNION ALL
SELECT *
FROM low_earners;

Example 3: Recursive CTE

A recursive CTE is useful for hierarchical data or iterative queries.

WITH RECURSIVE employee_hierarchy AS (
        SELECT employee_id, manager_id, name
        FROM employees
                WHERE manager_id IS NULL -- Start with the top-level manager
                UNION ALL
                SELECT e.employee_id, e.manager_id, e.name
                FROM employees e
                INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT *
FROM employee_hierarchy;

When to Use WITH:

• Simplify and organize complex queries.
• Avoid repeating subqueries.
• Improve query readability and maintainability.