Common Table Expression (CTE)

In Oracle SQL, a Common Table Expression (CTE) is a powerful tool that allows you to define a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs can help simplify complex queries, improve readability, and enable recursive queries.

Basic Syntax of a CTE

WITH cte_name AS (
-- CTE query
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT *
FROM cte_name;

Key Features of CTEs

1. Temporary Result Set: CTEs exist only during the execution of the query.

2. Readability: They can make complex SQL queries easier to read and understand.

3. Recursion: CTEs can be recursive, allowing you to work with hierarchical data.

Example of Using CTE in Oracle

Step 1: Create a Sample Table

Let's create an Employees table.

CREATE TABLE Employees (
id NUMBER PRIMARY KEY,
name VARCHAR2(100),
salary NUMBER,
department_id NUMBER
);

Step 2: Insert Sample Data

Insert some sample data into the Employees table.

INSERT INTO Employees (id, name, salary, department_id) VALUES (1, 'Alice', 70000, 1);
INSERT INTO Employees (id, name, salary, department_id) VALUES (2, 'Bob', 60000, 1);
INSERT INTO Employees (id, name, salary, department_id) VALUES (3, 'Charlie', 80000, 2);
INSERT INTO Employees (id, name, salary, department_id) VALUES (4, 'David', 50000, 2);
INSERT INTO Employees (id, name, salary, department_id) VALUES (5, 'Eve', 90000, 3);

Step 3: Use a CTE to Calculate Average Salary by Department

Here’s an example of how to use a CTE to calculate the average salary of employees by department.

WITH AverageSalary AS (
SELECT
department_id,
AVG(salary) AS avg_salary
FROM
Employees
GROUP BY
department_id
)
SELECT
department_id,
avg_salary
FROM
AverageSalary
WHERE
avg_salary > 60000;

Explanation of the CTE Query

1. CTE Definition:

The CTE is defined using the WITH clause.

It calculates the average salary for each department by grouping the results based on department_id.

1. Main Query:

The main query selects department_id and avg_salary from the CTE.

It filters the results to show only those departments where the average salary is greater than 60,000.

Expected Output

When you run the CTE query, the output should look something like this:

DEPARTMENT_ID AVG_SALARY

---

1 65000
2 65000
3 90000

Recursive CTE Example

CTEs can also be recursive, allowing you to work with hierarchical data. Here’s an example of a recursive CTE that generates a simple hierarchy.

Step 1: Create a Sample Hierarchical Table

CREATE TABLE EmployeesHierarchy (
id NUMBER PRIMARY KEY,
name VARCHAR2(100),
manager_id NUMBER
);

Step 2: Insert Sample Data

INSERT INTO EmployeesHierarchy (id, name, manager_id) VALUES (1, 'Alice', NULL); -- CEO
INSERT INTO EmployeesHierarchy (id, name, manager_id) VALUES (2, 'Bob', 1); -- Manager
INSERT INTO EmployeesHierarchy (id, name, manager_id) VALUES (3, 'Charlie', 2); -- Employee
INSERT INTO EmployeesHierarchy (id, name, manager_id) VALUES (4, 'David', 2); -- Employee
INSERT INTO EmployeesHierarchy (id, name, manager_id) VALUES (5, 'Eve', 1); -- Manager

Step 3: Use a Recursive CTE

WITH RECURSIVE EmployeeCTE AS (
SELECT id, name, manager_id, 0 AS level
FROM EmployeesHierarchy
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, level + 1
FROM EmployeesHierarchy e
INNER JOIN EmployeeCTE ec ON e.manager_id = ec.id
)
SELECT *
FROM EmployeeCTE
ORDER BY level, id;

Conclusion

CTEs in Oracle SQL are a powerful feature that can simplify complex queries and make your SQL code easier to read. They can be used for various tasks, including aggregations and hierarchical queries. Always ensure to test your queries with appropriate data for better understanding and verification.