Using WITH Clause (CTEs) to Write Readable Complex Queries

Taming Complex SQL

The WITH clause, also known as Common Table Expressions (CTEs), lets you define named subqueries at the beginning of a SQL statement and reference them like tables in the main query. This transforms deeply nested subqueries into a flat, top-to-bottom structure that is far easier to read, debug, and maintain.

Basic CTE Syntax

WITH active_employees AS (
    SELECT employee_id, first_name, last_name, salary, department_id
      FROM employees
     WHERE status = 'ACTIVE'
       AND hire_date < ADD_MONTHS(SYSDATE, -6)
),
dept_averages AS (
    SELECT department_id,
           ROUND(AVG(salary), 2) AS avg_salary,
           COUNT(*) AS emp_count
      FROM active_employees
     GROUP BY department_id
)
SELECT ae.first_name, ae.last_name, ae.salary,
       da.avg_salary AS dept_avg,
       ae.salary - da.avg_salary AS diff_from_avg
  FROM active_employees ae
  JOIN dept_averages da ON ae.department_id = da.department_id
 WHERE ae.salary > da.avg_salary * 1.2
 ORDER BY diff_from_avg DESC;

Each CTE is like a temporary view that exists only for the duration of the query. CTEs can reference earlier CTEs in the same WITH clause, building up complexity step by step.

Recursive CTEs

Oracle supports recursive CTEs for hierarchical queries, providing a standard SQL alternative to CONNECT BY:

WITH org_chart (employee_id, name, manager_id, lvl, path) AS (
    -- Anchor: top-level managers
    SELECT employee_id,
           first_name || ' ' || last_name,
           manager_id, 1,
           first_name || ' ' || last_name
      FROM employees
     WHERE manager_id IS NULL
    UNION ALL
    -- Recursive: employees under each manager
    SELECT e.employee_id,
           e.first_name || ' ' || e.last_name,
           e.manager_id, oc.lvl + 1,
           oc.path || ' > ' || e.first_name || ' ' || e.last_name
      FROM employees e
      JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT LPAD(' ', (lvl - 1) * 4) || name AS org_hierarchy, lvl
  FROM org_chart
 ORDER BY path;

PL/SQL Declaration Section CTEs (12c+)

Oracle 12c introduced the ability to define PL/SQL functions directly within the WITH clause, which is useful for complex calculations that need procedural logic without creating a permanent database function:

WITH FUNCTION calculate_bonus(p_salary NUMBER, p_rating VARCHAR2) RETURN NUMBER IS
BEGIN
    RETURN CASE p_rating
        WHEN 'A' THEN p_salary * 0.20
        WHEN 'B' THEN p_salary * 0.10
        WHEN 'C' THEN p_salary * 0.05
        ELSE 0 END;
END;
SELECT employee_id, salary, performance_rating,
       calculate_bonus(salary, performance_rating) AS bonus
  FROM employees
/

Performance Considerations

CTEs are not automatically materialized in Oracle (unlike some other databases). The optimizer may inline them, merge them, or materialize them based on cost. If you need to force materialization to avoid repeated evaluation of an expensive CTE, add the /*+ MATERIALIZE */ hint inside the CTE.