SQL WITH Clause

SQL WITH Clause (Common Table Expressions)

The SQL WITH clause, also known as a Common Table Expression (CTE), is used to create a temporary named result set that can be referenced multiple times within the main SQL query. It improves readability and efficiency. The WITH clause was introduced by Oracle in Oracle 9i Release 2 and is now widely supported across MySQL 8+, PostgreSQL, SQL Server, and more.

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

Why Use the WITH Clause?

• Enhances code readability.
• Improves performance by reducing redundant subquery execution.
• Allows modular, reusable query writing.
• Helps optimize and simplify complex queries.

Syntax of the SQL WITH Clause

-- Single CTE
WITH alias_name AS (sql_subquery_statement)
SELECT column_list FROM alias_name [WHERE condition];

-- Multiple CTEs
WITH alias_A AS (subquery_1),
     alias_B AS (subquery_2)
SELECT column_list FROM alias_A, alias_B [WHERE join_condition];

Example: Employee Table

Consider the following Employee table:

Let us use the WITH clause to calculate the average salary per department:

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 CTE calculates the average salary for each department.
• The main query retrieves each employee name, department, and the average salary.
• The JOIN matches each employee with their department average.

Recursive CTE Example

A recursive CTE references itself, which is perfect for hierarchical data like an employee-manager tree or generating number sequences.

WITH RECURSIVE Numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM Numbers WHERE n < 5
)
SELECT * FROM Numbers;
-- Output: 1, 2, 3, 4, 5

WITH Clause vs Subquery vs Temporary Table

• CTE (WITH): Readable, reusable within one query, supports recursion.
• Subquery: Inline, but harder to read when nested deeply.
• Temp Table: Persists across multiple queries in a session, better for very large intermediate results.

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

Conclusion

The SQL WITH clause is essential for writing cleaner, optimized, and modular SQL. It breaks complex queries into smaller reusable blocks and even supports recursion for hierarchical data. For more SQL tutorials and database optimization tips, stay connected with .

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 cte
recursive cte sql
sql with clause example
common table expression sql