Explain CTEs (the WITH clause) in Oracle SQL

How it works

A CTE (the WITH clause) names a subquery upfront so the main query can reference it — potentially more than once — by name, which reads more clearly than deeply nested inline subqueries and can let the optimizer materialize the result once instead of re-evaluating it. Oracle also supports a recursive form (WITH ... AS (... UNION ALL ...)) for hierarchical or graph-style traversal, an alternative to CONNECT BY.

WITH high_earners AS (
  SELECT employee_id, department_id, salary
  FROM employees
  WHERE salary > 10000
)
SELECT d.department_name, COUNT(*) AS high_earner_count
FROM high_earners h
JOIN departments d ON d.department_id = h.department_id
GROUP BY d.department_name;

Related Questions