The SQL WITH clause, also known as Common Table Expressions (CTEs), was introduced in Oracle 9i Release 2. It allows you to define a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. This makes complex queries easier to read and debug.

What is the SQL WITH Clause?

The WITH clause defines a temporary relation (or sub-query) that can be used multiple times within the main SQL query. It simplifies queries by breaking them into modular, reusable components. Note that not all database systems support the WITH clause.

Syntax

WITH temporaryTable (columnName) AS (
    SELECT columnName
    FROM Table
)
SELECT columnName
FROM Table, temporaryTable
WHERE Table.columnName > temporaryTable.columnName;

Explanation

1. Temporary Table Definition: The WITH clause creates a temporary table temporaryTable with a single column columnName derived from the Table.
2. Main Query: The subsequent SELECT statement uses this temporary table to filter results based on a condition.

SQL WITH Clause Examples

Example 1: Finding Employees with Above-Average Salary

Table: Employee

Query:

WITH averageSalary AS (
    SELECT AVG(Salary) AS avgSalary
    FROM Employee
)
SELECT EmployeeID, Name, Salary
FROM Employee, averageSalary
WHERE Employee.Salary > averageSalary.avgSalary;

Output:

Explanation: This query finds employees whose salaries exceed the average salary calculated using the WITH clause.

Example 2: Finding Airlines with High Pilot Salaries

Table: Pilot

Query:

WITH totalSalary AS (
    SELECT Airline, SUM(Salary) AS total
    FROM Pilot
    GROUP BY Airline
),
averageSalary AS (
    SELECT AVG(Salary) AS avgSalary
    FROM Pilot
)
SELECT Airline
FROM totalSalary, averageSalary
WHERE totalSalary.total > averageSalary.avgSalary;

Output:

Explanation: This query identifies airlines where the total pilot salary exceeds the average salary of all pilots.

Important Points About SQL WITH Clause

• Readability and Debugging: The WITH clause simplifies complex queries by breaking them into smaller, more manageable pieces.
• Performance: It can improve query performance by reusing temporary results, reducing the need to re-evaluate sub-queries.
• Flexibility: Acts as a drop-in replacement for sub-queries, making complex SQL queries easier to manage.