How to use the WITH clause in Oracle

The WITH clause in Oracle, also known as subquery factoring, enables defining common table expressions (CTEs) for improved query readability and performance. 1. The basic syntax uses WITH cte_name AS (SELECT ...) followed by a main query referencing the CTE. 2. A single CTE example computes average salary once and compares individual salaries using CROSS JOIN. 3. Multiple CTEs can be defined comma-separated, such as calculating department salary totals then filtering those above average. 4. Recursive CTEs in Oracle 11gR2 use UNION ALL with anchor and recursive members, along with SEARCH and CYCLE clauses for hierarchical data like employee-manager trees. 5. Benefits include reusable logic, better readability, potential materialization for performance, and optional hints like / MATERIALIZE /. 6. WITH can be used in DML statements, such as updating salaries below department average. CTEs are temporary, can reference prior CTEs, support multiple references, require proper recursive structure, and work in SELECT, INSERT, UPDATE, DELETE but not directly in CREATE VIEW, making SQL cleaner and more efficient.

The WITH clause in Oracle, also known as the subquery factoring clause, allows you to define one or more named subqueries (common table expressions or CTEs) that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It improves query readability and performance by breaking complex queries into simpler, reusable parts.

Here’s how to use the WITH clause effectively in Oracle:

1. Basic Syntax

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

• The WITH clause defines one or more CTEs before the main query.
• Each CTE acts like a temporary view that exists only during the execution of the query.

2. Simple Example: Single CTE

Suppose you want to find employees whose salary is above the average:

WITH avg_salary AS (
    SELECT AVG(salary) AS avg_sal
    FROM employees
)
SELECT e.employee_id, e.first_name, e.salary
FROM employees e
CROSS JOIN avg_salary
WHERE e.salary > avg_salary.avg_sal;

• The avg_salary CTE computes the average once.
• The main query references it to compare individual salaries.

Note: Use CROSS JOIN when the CTE returns a single row (like an aggregate), or use a regular JOIN if joining on keys.

3. Multiple CTEs

You can define multiple CTEs in one WITH clause, separated by commas:

WITH dept_costs AS (
    SELECT d.department_name, SUM(e.salary) AS total_sal
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    GROUP BY d.department_name
),
avg_cost AS (
    SELECT AVG(total_sal) AS avg_sal
    FROM dept_costs
)
SELECT department_name, total_sal
FROM dept_costs, avg_cost
WHERE total_sal > avg_cost.avg_sal;

• First CTE calculates total salary per department.
• Second uses the first to compute average department cost.
• Main query filters departments above average.

4. Recursive Queries (Oracle 11gR2 and Later)

Oracle supports recursive CTEs using SEARCH and CYCLE clauses. Useful for hierarchical data like org charts.

Example: Show all employees and their managers recursively:

WITH emp_hierarchy (employee_id, first_name, manager_id, level) AS (
    -- Anchor member: top-level employees (no manager)
    SELECT employee_id, first_name, manager_id, 1
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member: employees under previous level
    SELECT e.employee_id, e.first_name, e.manager_id, eh.level   1
    FROM employees e
    JOIN emp_hierarchy eh ON e.manager_id = eh.employee_id
)
SEARCH DEPTH FIRST BY employee_id SET order_by_seq
CYCLE employee_id SET is_cycle TO 'Y' DEFAULT 'N'
SELECT LPAD(' ', 2*(level-1)) || first_name AS hierarchy, level, is_cycle
FROM emp_hierarchy
ORDER BY order_by_seq;

• UNION ALL connects anchor and recursive parts.
• SEARCH DEPTH FIRST defines traversal order.
• CYCLE detects loops in data.

5. Performance Benefits and Best Practices

• Reusable logic: Avoid repeating complex subqueries.
• Improved readability: Break down large queries.
• Materialization: Oracle may materialize the CTE result (store it temporarily), improving performance when referenced multiple times.
• Use hints cautiously: You can apply hints like /* MATERIALIZE */ or /* INLINE */ to influence optimization:

WITH good_performers AS (
    SELECT /*  MATERIALIZE */ employee_id
    FROM performance_reviews
    WHERE rating >= 4
)
...

6. Using WITH in INSERT, UPDATE, DELETE

The WITH clause can also be used with DML statements.

Example: Update using a WITH query

WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department_id
)
UPDATE employees e
SET salary = salary * 1.1
WHERE salary < (SELECT avg_sal FROM dept_avg d WHERE d.department_id = e.department_id);

This gives a 10% raise to employees earning below their department average.

Key Points to Remember

• CTEs are not stored permanently — they exist only for the query duration.
• A CTE can reference earlier CTEs in the same WITH clause.
• You can reference a CTE multiple times in the main query.
• Recursive CTEs require UNION ALL and proper anchor/recursive structure.
• Works in SELECT, INSERT, UPDATE, DELETE — not in CREATE VIEW directly.

Using the WITH clause makes your SQL cleaner and often more efficient. Start with simple factoring and explore recursive options for hierarchical data. It's a powerful tool in Oracle SQL.

The above is the detailed content of How to use the WITH clause in Oracle. For more information, please follow other related articles on the PHP Chinese website!