Site icon Radu Pârvu

Understanding CTEs in Oracle SQL for Better Query Management

Radu PARVU

Introduction

We continue with another powerful Oracle feature which is unjustly ignored sometimes:

Oracle SQL provides a powerful feature called the Common Table Expression (CTE) using the WITH clause.
It allows you to create temporary named result sets that exist only during the execution of a query.

What Is a CTE?

A CTE (Common Table Expression) is essentially a temporary query block.

Instead of nesting subqueries deeply inside a statement, you define them once at the top using the keyword  WITH. Think of it as:

“Create a temporary result set first, then query from it.”

Syntax

Basic Syntax

The basic syntax is:

WITH cte_name AS (
    SELECT ...
)
SELECT ...
FROM cte_name;

or:

WITH temporary_result AS (
    SELECT column1, column2
    FROM employees
)
SELECT *
FROM temporary_result;

Benefits

A CTE makes SQL:

Why Use WITH Instead of Subqueries?

Without CTE the SQL would look like:

SELECT *
FROM (
    SELECT employee_id, salary
    FROM employees
    WHERE salary > 5000
);

With CTE the code is much more easier to read:

WITH high_salary_employees AS (
    SELECT employee_id, salary
    FROM employees
    WHERE salary > 5000
)
SELECT *
FROM high_salary_employees;

it is obvious that the second version is:

Simple Example

Suppose we have this table:

| EMPLOYEE_ID | NAME | DEPARTMENT | SALARY |
| ----------- | ---- | ---------- | ------ |
| 1           | John | IT         | 7000   |
| 2           | Sara | HR         | 5000   |
| 3           | Mike | IT         | 9000   |
| 4           | Emma | FINANCE    | 8000   |

in the lab case:

Goal or Problem to solve

Find employees earning more than the department average!

Solution: Using WITH

WITH dept_avg AS (
    SELECT department,
           AVG(salary) AS avg_salary
    FROM employees_demo
    GROUP BY department
)
SELECT e.emp_name,
       e.department,
       e.salary,
       d.avg_salary
FROM employees_demo e
JOIN dept_avg d
    ON e.department = d.department
WHERE e.salary > d.avg_salary;

Step-by-Step Execution

Step 1 — Oracle Executes the CTE

SELECT department,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department

resulting something like:

DEPARTMENTAVG_SALARY
IT8000
HR5000
FINANCE8000

Step 2 — Main Query Uses the result from Step 1

Oracle joins employees with the temporary result for the final output being something like:

NAMEDEPARTMENTSALARYAVG_SALARY
MikeIT90008000

in my lab environment:

EMP_NAME                                           DEPARTMENT                         SALARY AVG_SALARY
-------------------------------------------------- ------------------------------ ---------- ----------
John                                               HR                                   7000       5000
Nicusor                                            IT                                   7000       4000

Multiple CTEs

You can define multiple temporary result sets: Sample Example:

WITH
dept_avg AS (
    SELECT department,
           AVG(salary) avg_salary
    FROM employees_demo
    GROUP BY department
),
high_paid AS (
    SELECT *
    FROM employees_demo
    WHERE salary > 7000
)
SELECT h.emp_name,
       h.salary,
       d.avg_salary
FROM high_paid h
JOIN dept_avg d
    ON h.department = d.department;

CTE Referencing Another CTE

this technique allows to create a pipeline-like structure.

A CTE can reference a previous CTE, below is a sample draft code snippet:

WITH
sales_2025 AS (
    SELECT *
    FROM sales
    WHERE sale_year = 2025
),
top_sales AS (
    SELECT *
    FROM sales_2025
    WHERE amount > 10000
)
SELECT *
FROM top_sales;

Real-World Reporting Example

Suppose you want to report the following to the executive level:

Sample Query would be something like:

WITH monthly_sales AS (
    SELECT region,
           SUM(amount) total_sales
    FROM sales
    WHERE sale_date >= DATE '2025-01-01'
    GROUP BY region
),
grand_total AS (
    SELECT SUM(total_sales) overall_sales
    FROM monthly_sales
)
SELECT m.region,
       m.total_sales,
       ROUND(
           (m.total_sales / g.overall_sales) * 100,
           2
       ) AS percentage
FROM monthly_sales m
CROSS JOIN grand_total g;

*note: example above is given for illustration purposes only

Recursive CTEs in Oracle

Oracle supports recursive queries using CTEs.

This is useful for following type use cases:

Example EMPLOYEE_HIERARCHY

EMP_IDNAMEMANAGER_ID
1CEONULL
2Manager A1
3Manager B1
4Developer X2
5Developer Y2
WITH employee_tree (
    emp_id,
    name,
    manager_id,
    level_no
) AS (
    -- Anchor query
    SELECT emp_id,
           name,
           manager_id,
           1
    FROM employee_hierarchy
    WHERE manager_id IS NULL
    UNION ALL
    -- Recursive query
    SELECT e.emp_id,
           e.name,
           e.manager_id,
           t.level_no + 1
    FROM employee_hierarchy e
    JOIN employee_tree t
        ON e.manager_id = t.emp_id
)
SELECT *
FROM employee_tree;

We defined levels in line with corporate organization:

Understanding Recursive Parts

A recursive CTE has two sections:

Anchor Query

Starting point. In our example above:

SELECT ...
WHERE manager_id IS NULL

Recursive Query

Keeps calling itself

SELECT ...
FROM employee_hierarchy e
JOIN employee_tree t

Oracle repeats until no more results are found.

CTE vs Temporary Table

As there is an common design decision to choose bet went eh two of them, hhere is a brief comparison table:

FeatureCTETemporary Table
Exists permanently?NoYes
Stored physically?Usually noYes
ScopeSingle querySession/transaction
PerformanceGood for logicBetter for huge datasets
Easy to readExcellentModerate

Performance Considerations

Please be aware that Oracle May:

Inline the CTE

Materialize the CTE

Optimize it differently

Materialization Hint

Also the materialisation hint is used sometimes for tuning isolated SQL cases, sample SQL:

WITH temp_data AS (
    SELECT /*+ MATERIALIZE */
           *
    FROM huge_table
)
SELECT *
FROM temp_data;

also the inline hint is sometimes used:

WITH temp_data AS (
    SELECT /*+ INLINE */
           *
    FROM huge_table
)
SELECT *
FROM temp_data;

Question: Does Oracle Always Materialize CTEs?

No. Oracle optimizer decides.

Common Use Cases

Best Practices

Use Meaningful Names!

Good:

WITH monthly_sales AS (...)

Bad:

WITH x AS (...)

Keep Each CTE Focused

Each CTE should solve one logical step.

Use Multiple CTEs for Readability

Avoid giant monolithic queries!

Comment Complex Logic

WITH
-- Calculate yearly revenue
yearly_revenue AS (...)

When NOT to Use CTEs

Avoid excessive CTE nesting when:

Sometimes inline views or temp tables are better.

Key Takeaways

WITH Clause Benefits

Exit mobile version