PL/SQL WITH Clause

The PL/SQL WITH clause is a powerful feature that enhances the readability and performance of your SQL queries. It allows you to define temporary result sets, which can be referenced multiple times within a single query.

This feature is particularly useful for simplifying complex queries and improving performance, especially in scenarios where subqueries are involved.

What is the PL/SQL WITH Clause?

The WITH clause, also known as subquery factoring, is used to define one or more subqueries that can be referred to by name within the main query. This approach reduces redundancy, as the subquery does not need to be repeated multiple times in the main query, leading to clearer and more maintainable code.

Syntax :

WITH subquery_name AS (

SELECT ...

FROM ...

WHERE ...

)

SELECT ...

FROM subquery_name

WHERE ...;

Key Terms:

• subquery_name: The name assigned to the subquery.
  
• SELECT: The main query that uses the result set defined by the WITH clause.

Examples of PL/SQL WITH Clause

Let's create two tables, employees and departments, and populate them with some data to demonstrate the usage of the WITH clause.The query creates the employees table with columns for employee ID, name, department ID, and salary, and then inserts three employee records into the table.

1. Employee Table

Query:

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    department_id NUMBER,
    salary NUMBER
);

INSERT INTO employees (emp_id, emp_name, department_id, salary) VALUES (1, 'John Doe', 101, 50000);
INSERT INTO employees (emp_id, emp_name, department_id, salary) VALUES (2, 'Jane Smith', 102, 60000); 
INSERT INTO employees (emp_id, emp_name, department_id, salary) VALUES (3, 'Mike Johnson', 101, 55000);

Output:

Explanation:

The output displays the inserted data, showing each employee's ID, name, department, and salary, confirming that the table was successfully populated with the specified information.

The output represents the data stored in the employees table after executing the insert queries.This output confirms that the data has been correctly inserted into the employees table, reflecting the specified employee details.

2. Department Table

Query:

CREATE TABLE departments (  
department_id NUMBER PRIMARY KEY, 
department_name VARCHAR2(50) );    

INSERT INTO departments (department_id, department_name) VALUES (101, 'HR');  
INSERT INTO departments (department_id, department_name) VALUES (102, 'Finance');  
INSERT INTO departments (department_id, department_name) VALUES (103, 'IT');

Output:

Explanation:

The query creates the departments table with two columns: department_id (the primary key) and department_name. Three departments—HR, Finance, and IT—are then inserted into the table.

The output displays these departments with their corresponding IDs, confirming that the table was successfully created and populated with the specified department data.

Example 1: Selecting Employees with Department Names

Let's select the employee names along with their respective department names using the WITH clause. The WITH clause creates a temporary result set named emp_dept, which joins the employees and departments tables. The main query then selects all the records from emp_dept.

Query:

WITH emp_dept AS (
    SELECT e.emp_name, d.department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
)
SELECT * FROM emp_dept;

Output:

Explanation:

• The output lists employee names alongside their respective department names.
  
• Employees from the same department are grouped together.

Example 2: Calculating Average Salary by Department

Now, let's calculate the average salary for each department. The WITH clause defines dept_avg_salary as a result set that calculates the average salary for each department.

The main query then joins this result set with the departments table to get the department names along with the average salary.

Query:

WITH dept_avg_salary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT d.department_name, das.avg_salary
FROM dept_avg_salary das
JOIN departments d ON das.department_id = d.department_id;

Output:

Explanation:

• The output displays each department's name with its calculated average salary.
  
• Only departments with employees are shown.

Example 3: Filtering High-Earning Employees

Let's filter employees earning more than the average salary of their respective departments. The WITH clause creates a result set avg_salaries containing the average salary for each department.

The main query joins this result set with the employees and departments tables, filtering employees whose salaries exceed the average salary of their respective departments.

Query:

WITH avg_salaries AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.emp_name, e.salary, d.department_name
FROM employees e
JOIN avg_salaries a ON e.department_id = a.department_id
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > a.avg_salary;

Output:

Explanation:

• The output shows employees earning more than the average salary in their department.
  
• It includes the employee's name, salary, and corresponding department name

Conclusion

The PL/SQL WITH clause is an essential tool for writing efficient and readable SQL queries. By allowing the definition of reusable subqueries, it reduces code duplication and improves query performance. Whether you're joining tables, calculating aggregates, or filtering data, the WITH clause can simplify complex operations.