SQL WITH Clause

The SQL WITH clause, also known as Common Table Expressions (CTEs), is a powerful tool that simplifies complex SQL queries, improves readability, and enhances performance by defining temporary result sets that can be reused multiple times. Whether we’re working on aggregating data, analyzing large datasets, or building complex reports, understanding how to use the WITH clause in SQL can significantly improve your querying experience.

In this article, we will explain what the SQL WITH clause is, how it works, and provide practical SQL WITH clause examples to show its flexibility. We will cover scenarios such as finding employees with above-average salaries and identifying airlines with high pilot salaries, providing both the syntax and expected output for each.

What is the SQL WITH Clause ?

The SQL WITH clause is used to define temporary tables or result sets within a query. These temporary relations (also called Common Table Expressions (CTEs)) can then be referenced in the main query multiple times, allowing for more readable and maintainable SQL code. The WITH clause essentially makes it easier to write complex queries by breaking them down into manageable subqueries.

This method also helps in performance optimization, as the query planner can optimize the reuse of intermediate results instead of re-executing the same complex subqueries multiple times.

Syntax

WITH temporaryTable (averageValue) AS (
    SELECT AVG (Attr1)
    FROM Table
)
    SELECT Attr1
    FROM Table, temporaryTable
    WHERE Table.Attr1 > temporaryTable.averageValue;

Key Terms

The WITH clause defines a temporary relation (temporaryTable), which contains values selected from some_table.
The subsequent SELECT query uses this temporary table in the main query to perform a join or filter data based on specific conditions.

Note

When a query with a WITH clause is executed, first the query mentioned within the clause is evaluated and the output of this evaluation is stored in a temporary relation. Following this, the main query associated with the WITH clause is finally executed that would use the temporary relation produced.

Key Terms

SQL WITH Clause Examples

Let us look at some of the examples of WITH Clause in SQL to better understand how it can simplify complex queries and improve query performance:

Example 1: Finding Employees with Above-Average Salary

This example demonstrates how to find all employees whose salary is higher than the average salary of all employees in the database. The query calculates the average salary using the WITH clause and compares each employee’s salary against this average to return those with above-average salaries.

Employee Table

EmployeeID	Name	Salary
100011	Smith	50000
100022	Bill	94000
100027	Sam	70550
100845	Walden	80000
115585	Erik	60000
1100070	Kate	69000

Query

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

Output

EmployeeID	Name	Salary
100022	Bill	94000
100845	Walden	80000

Explanation

The WITH clause calculates the average salary (averageSalary) for all employees.
The main SELECT statement retrieves employees whose salary is greater than the calculated average salary.
The average salary of all employees is 70591. Therefore, all employees whose salary is more than the obtained average lies in the output relation.

Example 2: Finding Airlines with High Pilot Salaries

In this example, we aim to find airlines where the total salary of all pilots exceeds the average salary of all pilots in the database. The WITH clause will be used to first calculate the total salary for each airline and then compare it to the overall average salary.

Pilot Table

EmployeeID	Airline	Name	Salary
70007	Airbus 380	Kim	60000
70002	Boeing	Laura	20000
10027	Airbus 380	Will	80050
10778	Airbus 380	Warren	80780
115585	Boeing	Smith	25000
114070	Airbus 380	Katy	78000

Query

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

Output

Airline
Airbus 380

Explanation

The total salary of all pilots of Airbus 380 = 298,830 and that of Boeing = 45000. Average salary of all pilots in the table Pilot = 57305. Since only the total salary of all pilots of Airbus 380 is greater than the average salary obtained, so Airbus 380 lies in the output relation.

Important Points About SQL | WITH Clause

The SQL WITH clause is good when used with complex SQL statements rather than simple ones
It also allows us to break down complex SQL queries into smaller ones which make it easy for debugging and processing the complex queries.
The SQL WITH clause is basically a drop-in replacement to the normal sub-query.
The SQL WITH clause can significantly improve query performance by allowing the query optimizer to reuse the temporary result set, reducing the need to re-evaluate complex sub-queries multiple times.

Conclusion

The SQL WITH clause (Common Table Expressions) is an essential tool for simplifying complex queries in SQL. By breaking down queries into smaller, more manageable parts, the WITH clause enhances the readability, maintainability, and performance of SQL queries. Whether we are calculating aggregates, filtering data, or performing complex joins, the WITH clause can streamline our query logic and improve execution efficiency.