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. 

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

Query: 

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

Output

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

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.

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.

FAQs

What is a with clause in SQL?

The WITH clause in SQL, allows us to define a temporary named result set that can be referenced multiple times within a main SQL query.

What is SQL using clause?

The USING clause in SQL is a join condition that simplifies syntax by automatically identifying columns that are common between two tables in a join operation

What is the difference between CTE and with clause?

CTE (Common Table Expression) refers to the actual temporary named result set created within a query, which can be used as a virtual table for the duration of that query. The WITH clause is the SQL syntax used to define a CTE. In other words, the WITH clause is the statement that creates the CTE, allowing you to assign a name to a sub-query and reuse it within the main query.

GeeksforGeeks

Improve

Article Tags :

• Databases
• DBMS
• SQL
• DBMS-SQL
• SQL-basics

+1 More