How to Use the WITH Clause in Oracle SQL

Long queries built from nested subqueries read from the inside out: you find the innermost query, work out what it returns, then move one level up. The WITH clause turns that around. It names each subquery at the top of the statement, so the query reads from top to bottom, one step at a time, and each step can be used again by the steps after it.

This guide covers common table expressions (CTEs), chaining them, naming their columns, and using one CTE more than once.

Code for This Guide

The main examples are in the examples/with-clause folder of the Oracle Database 26ai code repository on GitHub, each with its output. They query NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

The WITH clause is also called subquery factoring, and each named subquery is a common table expression.

Syntax:

with name [(column [, ...])] as (subquery)
   [, name [(column [, ...])] as (subquery) ...]
select ...

A CTE exists only for the statement that defines it. The main query and any later CTE can read it like a table.

Build a Query in Steps

This query works in two named steps: dept_pay computes staff and average salary per department, and big_depts keeps the departments with at least ten people. The main query joins the result to DEPARTMENTS for the names.

Example:

with dept_pay as (
  select department_id, count(*) as staff, round(avg(salary)) as avg_salary
  from   employees
  group  by department_id
),
big_depts as (
  select * from dept_pay where staff >= 10
)
select d.department_name, b.staff, b.avg_salary
from   big_depts b join departments d on d.department_id = b.department_id;

Output:

DEPARTMENT_NAME           STAFF    AVG_SALARY
______________________ ________ _____________
Flight Operations            12         17817
Cabin Services               11          9009
Sales and Marketing          10         14210

big_depts reads dept_pay, which is defined before it. A CTE can refer to any CTE defined earlier in the same WITH clause, but not to one defined later.

Name the Columns

Instead of aliasing every expression inside the subquery, you can list the column names after the CTE's name. The list must have one name for each column the subquery selects.

Example:

with dept_pay (dept, staff, avg_salary) as (
  select department_id, count(*), round(avg(salary))
  from   employees
  group  by department_id
)
select dept, staff, avg_salary
from   dept_pay
where  avg_salary > 15000
order  by avg_salary desc;

Output:

   DEPT    STAFF    AVG_SALARY
_______ ________ _____________
     10        2         42000
     20       12         17817
     50        4         16400
     60        3         15867
     70        3         15800

The column list is optional for ordinary CTEs and required for recursive ones.

Use a CTE More Than Once

A CTE can be read several times in the same query. Here monthly counts bookings per month, and the main query reads it once for each month's count and again, in a scalar subquery, for the total that turns each count into a percentage.

Example:

with monthly as (
  select to_char(booked_at, 'YYYY-MM') as month, count(*) as bookings
  from   bookings group by to_char(booked_at, 'YYYY-MM')
)
select m.month, m.bookings, round(100 * m.bookings / (select sum(bookings) from monthly), 1)
         as pct_of_all
from   monthly m
order  by m.month;

Output:

MONTH         BOOKINGS    PCT_OF_ALL
__________ ___________ _____________
2025-10              7             1
2025-11             81          11.6
2025-12            164          23.4
2026-01            222          31.7
2026-02            154            22
2026-03             72          10.3

6 rows selected.

When a CTE is used more than once, the optimizer may compute it once and keep the rows in a temporary table, instead of running the subquery for every reference. It decides by cost; the hint /*+ materialize */ inside the CTE forces it, and /*+ inline */ prevents it.

WITH Compared with Inline Views

Both produce the same results; choose WITH whenever a query has more than one level of nesting or repeats a subquery.

Related Guides

• How to Use Subqueries in Oracle SQL
• How to Filter Groups with HAVING in Oracle SQL

Conclusion

The WITH clause names subqueries at the top of a statement so a long query reads as a series of steps. Chain CTEs by referring to earlier ones, name their columns in a list when it is clearer, and reuse a CTE as often as you need; the optimizer can compute it once for all its references.