SQL WITH Clause

SQL WITH Clause

The SQL WITH clause is used to create a sub-query block that can be referenced multiple times within the main SQL query. This helps improve query readability and efficiency. The WITH clause was introduced by Oracle in Oracle 9i Release 2 and is widely supported in various SQL databases today.

Complete Python Course with Advance topics:-Click Here
SQL Tutorial :-Click Here

Why Use the WITH Clause?

• Enhances code readability.
• Improves query performance by reducing redundant sub-query execution.
• Allows modular query writing.
• Helps in complex query optimization.

Syntax for the SQL WITH Clause

Using a Single Sub-query Alias

WITH <alias_name> AS (sql_sub-query_statement)  
SELECT column_list FROM <alias_name> [table_name]  
[WHERE <join_condition>];  

Using Multiple Sub-query Aliases

WITH <alias_name_A> AS (sql_sub-query_statement),  
<alias_name_B> AS (sql_sub-query_statement_from_alias_name_A OR sql_sub-query_statement)  
SELECT <column_list>  
FROM <alias_name_A>, <alias_name_B>, [table_names]  
[WHERE <join_condition>];

Example: Employee Table

Consider the following Employee table:

Now, let’s use the WITH clause to calculate the average salary per department.

Query Using WITH Clause

WITH Dept_Salary AS (
    SELECT Department, AVG(Salary) AS Avg_Salary 
    FROM Employee 
    GROUP BY Department
)
SELECT e.Emp_Name, e.Department, ds.Avg_Salary 
FROM Employee e 
JOIN Dept_Salary ds 
ON e.Department = ds.Department;

Explanation:

• The Dept_Salary sub-query calculates the average salary for each department.
• The main SELECT query retrieves the employee name, their department, and the calculated average salary.
• The JOIN condition ensures that each employee is matched with their department’s average salary.

Download New Real Time Projects :-Click here
Complete Advance AI topics:- CLICK HERE

Conclusion

The SQL WITH clause is an essential feature for writing cleaner, optimized, and modular SQL queries. It simplifies complex queries by breaking them into smaller, reusable blocks. If you frequently work with sub-queries, using the WITH clause will make your SQL code more readable and efficient.

For more SQL tutorials and database optimization tips, stay connected with UpdateGadh!

with clause in sql w3schools
sql multiple with clause
sql server with clause
with clause in oracle sql
mysql with clause
with clause in postgresql
sql with as ( select)
sql with cte
with clause in sql
sql
sql with clause example