PL/SQL CTE

A Common Table Expression (CTE) in Oracle PL/SQL is a named temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It helps in simplifying complex queries and makes the code more readable and maintainable. CTEs were introduced in Oracle 9i.

Syntax

Here is a basic syntax for creating a CTE:

WITH cte_name (column1, column2, ...) AS (
    -- CTE query definition
    SELECT column1, column2, ...
    FROM your_table
    WHERE condition
)
-- Main query using the CTE
SELECT *
FROM cte_name;

Let’s break down the components of this syntax:

WITH: The WITH clause starts the definition of the CTE.
cte_name: This is the name given to the CTE, which you can then use to refer to the result set.
(column1, column2, …): Optional column list that specifies the names of the columns in the CTE. If not specified, the columns will be inferred from the SELECT statement in the CTE query.
AS: Keyword that separates the CTE name and the query definition.
SELECT column1, column2, … FROM your_table WHERE condition: This is the query that defines the CTE. It can be a complex query involving joins, aggregations, or any other valid SQL constructs.
After defining the CTE, you can use it in the main query, as shown in the example above.

Example

Here’s a more concrete example to illustrate the use of a CTE. Suppose you have a table named employees with columns employee_id, employee_name, and manager_id. You can use a CTE to find employees and their managers:

WITH EmployeeHierarchy AS (
    SELECT employee_id, employee_name, manager_id
    FROM employees
    WHERE manager_id IS NOT NULL
)
SELECT e.employee_name AS employee, m.employee_name AS manager
FROM employees e
JOIN EmployeeHierarchy m ON e.manager_id = m.employee_id;

In this example, the CTE (EmployeeHierarchy) is used to filter out employees who have a manager. The main query then joins the employees table with the CTE to retrieve the names of employees and their corresponding managers.

Using CTEs can enhance the readability of complex queries, break down large queries into smaller, more manageable parts, and even improve performance in some cases by allowing the database engine to optimize the execution plan.