In Oracle SQL, Common Table Expressions (CTEs), also known as subquery factoring or WITH clause, allow you to define temporary result sets that can be referenced within the main query. CTEs improve query readability, allow for better organization of complex SQL queries, and can be reused multiple times within the same query.

Syntax:

The basic syntax of a CTE in Oracle is as follows:

WITH cte_name AS (
-- Define the CTE subquery
SELECT column1, column2
FROM some_table
WHERE condition
)
-- Main query that references the CTE
SELECT *
FROM cte_name;

Example of a Simple CTE:

WITH dept_salary AS (
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
)
SELECT department_id, total_salary
FROM dept_salary
WHERE total_salary > 50000;

In this example:

The CTE dept_salary calculates the total salary per department.

The main query then filters departments where the total salary is greater than 50,000.

Types of Common Table Expressions:

1. Simple CTE:

Used to simplify and reuse subqueries. It is defined once and used in the main query.

1. Recursive CTE:

Oracle SQL also supports recursive CTEs, which are useful for hierarchical or tree-structured data (e.g., organizational charts, bill of materials).

A recursive CTE consists of two parts: a base query (non-recursive) and a recursive query that references the CTE itself.

Recursive CTE Example:

WITH employee_hierarchy (emp_id, emp_name, manager_id) AS (
-- Base case: select top-level managers (employees with no manager)
SELECT employee_id, first_name, manager_id
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive part: find all employees reporting to the managers
SELECT e.employee_id, e.first_name, e.manager_id
FROM employees e
JOIN employee_hierarchy h ON e.manager_id = h.emp_id
)
SELECT * FROM employee_hierarchy;

In this example:

The CTE employee_hierarchy starts with employees with no manager (top-level).

The recursive query joins each subsequent employee with their respective manager to build the full hierarchy.

Key Benefits of CTEs:

Improved Readability: CTEs can break down complex queries into logical, manageable parts.

Reusability: A CTE can be referenced multiple times within the main query.

Modular Query Design: Queries are easier to maintain and debug when structured using CTEs.

Performance: CTEs can sometimes optimize query performance, especially for recursive operations or when repeated subquery logic is involved.

Limitations:

CTEs are only valid for the duration of the query in which they are used.

CTEs cannot be indexed directly, but they benefit from indexes on the underlying tables.