How to Use a WITH Clause in Oracle
Last updated August 25, 2026 · By the SaturnSQL team
WITH (subquery factoring) names subqueries up front so the main query can reference them like tables, keeping long queries readable and reusable.
WITH regional_sales AS (
SELECT region_id, SUM(order_total) AS total_sales
FROM orders
GROUP BY region_id
)
SELECT r.region_id, r.total_sales
FROM regional_sales r
WHERE r.total_sales > 100000;Multiple CTEs
Later CTEs in the same WITH clause can reference earlier ones, chaining steps without nesting inline views.
WITH high_value_orders AS (
SELECT * FROM orders WHERE order_total > 500
),
repeat_customers AS (
SELECT customer_id, COUNT(*) AS order_count
FROM high_value_orders
GROUP BY customer_id
HAVING COUNT(*) > 1
)
SELECT * FROM repeat_customers;Materialization hints
Oracle's optimizer decides on its own whether to run a CTE once into a temporary result (materialize) or inline it into the main query plan each time it is referenced. /*+ MATERIALIZE */ and /*+ INLINE */ hints override that choice when the optimizer picks badly, typically when a CTE is referenced multiple times and recomputing it is expensive. Oracle only recognizes a hint in the comment immediately after the SELECT keyword, so the hint belongs inside the CTE's own SELECT, not after WITH.
WITH top_customers AS (
SELECT /*+ MATERIALIZE */ customer_id, SUM(order_total) AS lifetime_value
FROM orders
GROUP BY customer_id
)
SELECT * FROM top_customers WHERE lifetime_value > 10000;Oracle also supports recursive WITH clauses using the SEARCH and CYCLE syntax defined by the SQL standard, which is a separate mechanism from CONNECT BY and can express the same hierarchical queries in a portable way. A recursive WITH requires an explicit column alias list on the CTE, such as WITH cte (id, ...) AS (...); omitting it raises ORA-32039.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free