SQL | WITH Clause

SQL queries can sometimes be complex, especially when you need to deal with multiple nested subqueries, aggregations, and joins. This is where the SQL WITH clause also known as Common Table Expressions (CTEs) comes in to make life easier. The WITH Clause 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. Let’s dive into the basics and see how this powerful tool can simplify your SQL queries.

What is SQL WITH Clause?

The SQL WITH clause is used to define temporary tables or result sets within a query. These temporary relations also known as Common Table Expressions (CTEs), act like virtual tables that exist only during the execution of the query. We can use these temporary tables multiple times in the main query, making it easier to manage and reuse complex logic without repeating the same subquery.

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.

Why Use the WITH Clause?

• Improves Readability: By breaking down complex queries into smaller, more manageable parts.
• Enhances Maintainability: Makes it easier to debug and modify your queries.
• Optimizes Performance: Reduces redundancy and ensures that temporary results are only calculated once.

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. 

Examples of SQL WITH Clause

Let’s look at some practical 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

Query: 

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

Output

Explanation:

• Temporary Table (CTE): We calculate the average salary using the WITH clause and store it in a temporary table called averageSalary.
• Main Query: The main query then compares each employee’s salary against the calculated average and returns the employees whose salaries are above the average.
• 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

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

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.

Key Benefits of Using the WITH Clause

1. Improved Readability: The WITH clause breaks down complex queries into simpler parts, making it easier to follow the logic.

2. Reusable Subqueries: If you need to reference the same subquery multiple times in your query, the WITH clause saves you from repeating the same code.

3. Performance Optimization: By storing intermediate results, SQL databases can optimize the execution of queries, potentially improving performance.

4. Easy Debugging: Since each CTE is defined separately, it’s easier to test and debug different parts of the query without affecting the main logic.

Important Things to Remember About the SQL WITH Clause

1. Temporary Lifetime: The temporary tables (CTEs) defined in the WITH clause only exist during the execution of the query. Once the query is finished, they are discarded.

2. Nested WITH Clauses: You can define multiple CTEs in a single query, and they can reference each other.

Example:

WITH CTE1 AS (...), CTE2 AS (...)
SELECT * FROM CTE1, CTE2;

3. Performance Consideration: While the WITH clause is excellent for readability and maintainability, it can sometimes be less efficient in cases where the temporary result set is large. Always check the execution plan to ensure you’re optimizing your queries correctly.

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.