What is the With Clause in SQL Server | SQL WITH Clause Tricks

Summary: In this article, you will learn What is the With Clause in SQL Server. Let’s understand with an example in detail that How to break down complex SQL queries into smaller ones that make it simple for debugging & processing the most complex queries.

What is the With Clause in SQL Server?

SQL WITH clause or With Clause in SQL Server, also known as subquery refactoring or common table expressions(CTEs) is used for creating a temporary result set using a general SQL query, such that this temporary set can further be used multiple times within the main SELECT, INSERT, UPDATE or DELETE statements, i.e, SQL WITH clause creates a temporary virtual table with can be further used in main SQL queries.

Syntax:

-- Define the CTE(Temporary Table) name and column list
WITH Temp_TableName ( ColumnName1, ColumnName2, ...)
AS

-- Define the CTE query
(
SELECT ColumnName1, ColumnName2
FROM TableName1
WHERE condition
)

-- Define the main query
SELECT ColumnName1, ColumnName2
FROM Temp_TableName;

-- Define the CTE(Temporary Table) name and column list

WITH Temp_TableName ( ColumnName1, ColumnName2, ...)

-- Define the CTE query

(

SELECT ColumnName1, ColumnName2

FROM TableName1

WHERE condition

)

-- Define the main query

SELECT ColumnName1, ColumnName2

FROM Temp_TableName;

Parameters

There are different parameters used in the syntax are :

WITH: This With clause is used for the purpose of creating a common table expression or temporary tables
Temp_TableName ( ColumnName1, ColumnName2, …): Here, Temp_TableName is the name of CTE, and ( ColumnName1, ColumnName2, …) is the definition of column names of the CTE which we will be using further in the main query.
AS (SELECT ColumnName1, ColumnName2 FROM TableName1 WHERE condition): This section specifies a SELECT statement whose result set will populate the CTE.
SELECT ColumnName1, ColumnName2 FROM Temp_TableName: This section specifies the main outer query. The SELECT statement will use the columns from the resultant CTE and produces the final result.

Which parameters are mentioned above, all the parameters are mandatory. You may use WHERE GROUP BY, ORDER BY, and HAVING clauses based on your requirement.

Important Note: A CTE can’t describe another CTE. But if more than one CTE then the definition is required we can use set operators like UNION, UNION ALL, INTERSECT, and EXCEPT.

Benefits of Using the WITH Clause

The SQL WITH clause permits you to reduce joins and logically sequence your code. Furthermore, it does this by creating temporary tables (i.e. technically they are views) that are usable by your main SQL query statement so that you can break your code into smaller & easier-to-understand snippets.

How Does SQL WITH Clause work and How Uses it?

WITH clause permits us to provide a subquery block a name that can be utilized in multiple places within the main SELECT, INSERT, DELETE or UPDATE SQL query. Furthermore, the name assigned to the subquery is treated as though it was an inline view or a table.

It is most helpful when you required the identical or same set of results data multiple times. In that case, you can generally define a CTE for this data and reuse the identical again and again by referencing it. It’s a kind of code reuse.

Going ahead we’ll be discussing the above-mentioned WITH clause in good detail.

In order to demonstrate and describe the WITH clause in SQL effectively, we’ll be using the following “Orders” table. Basically, this table is made for the purpose of an e-commerce website. This table contains Order _ID, Customer_Names, City, and the details of the items purchased by them.

The schema for the as per above mentioned “orders” table is:

Number of Records: 10

Customers
Order_ID (Primary key)
Customer_Name
City
Items_Purchased
Amount_Paid
Order_Date

Let’s have a glance at the records in the orders table. in order that later, we can understand how

With Clause in SQL Server Helpful:

Order_ID	Customer_Name	City	Items_Purchased	Amount_Paid	Order_Date
1	Rhoda Report	Kolkata	Pendant switch	400	2020-04-11
2	Paige Turner	New Delhi	Batten nail	510	2020-04-21
3	Ray Sin	Mumbai	Modular Plate	455	2020-04-29
4	Hope Furaletter	New York	PVC Tape Roll	110	2020-05-08
5	Dee End	Mumbai	Nose Plier	780	2020-05-10
6	Eileen Dover	London	Screw Driver Kit	230	2020-05-14
7	Skye Blue	Michigan	Tube Rod	450	2020-05-20
8	Staum Clowd	Amsterdam	Ignitor HPSV	890	2020-05-26
9	Dave Allippa	London	Fan Regulator	900	2020-06-11
10	Mark Ateer	Kolkata	Fan Regulator	620	2020-06-15

Examples of With Clause in SQL Server

Here are a some examples to understand WITH clause in SQL.

Example #1

Find out the average number of orders placed per month for every category of an item sold-out at the e-commerce website.

Code:

WITH Orders_CTE (Order_ID, Number_of_Orders)
AS
(
SELECT Items_Purchased, COUNT(Order_ID) as Number_of_Orders
FROM Customers
GROUP BY Items_Purchased
)
SELECT AVG(Number_of_Orders) AS "Average Orders Per Category"
FROM Orders_CTE;

WITH Orders_CTE (Order_ID, Number_of_Orders)

(

SELECT Items_Purchased, COUNT(Order_ID) as Number_of_Orders

FROM Customers

GROUP BY Items_Purchased

)

SELECT AVG(Number_of_Orders) AS "Average Orders Per Category"

FROM Orders_CTE;

with clause in sql example

Example #2

Find out the total number of orders placed per month for every category item sold at the e-commerce website.

Code:

WITH Orders_CTE (item_category,Order_ID, order_month)
AS
(
SELECT Items_Purchased as item_category, Order_ID, MONTH(Order_Date) AS order_month
FROM Customers
WHERE Order_ID IS NOT NULL
)
SELECT item_category, COUNT(Order_ID) AS "Total Orders Placed",order_month
FROM Orders_CTE
GROUP BY order_month, item_category
ORDER BY item_category, order_month;

WITH Orders_CTE (item_category,Order_ID, order_month)

(

SELECT Items_Purchased as item_category, Order_ID, MONTH(Order_Date) AS order_month

FROM Customers

WHERE Order_ID IS NOT NULL

)

SELECT item_category, COUNT(Order_ID) AS "Total Orders Placed",order_month

FROM Orders_CTE

GROUP BY order_month, item_category

ORDER BY item_category, order_month;

example of SQL With clause
You can see in the given above example that we’ve first created a CTE of Orders. Furthermore, it’s a listing of all orders, their item category, and the month of order.

Next, we’ve described the main query referencing the Orders_CTE. Thus, it makes use of the orders_cte to group orders by item_category and order_month.

Example #3

Find out the total number of orders placed & the total revenue generated per month by different categories of items sold at the e-commerce website.

Code:

WITH Orders_CTE (item_category,Order_ID, order_month, Amount_Paid)
AS
(
SELECT Items_Purchased as item_category, Order_ID, MONTH(Order_Date) AS order_month, Amount_Paid
FROM Customers
WHERE Order_ID IS NOT NULL
)
SELECT item_category, COUNT(Order_ID) AS "Total Orders Placed",order_month, SUM(Amount_Paid)as "Total Revenue"
FROM Orders_CTE
GROUP BY order_month, item_category
ORDER BY item_category, order_month;

WITH Orders_CTE (item_category,Order_ID, order_month, Amount_Paid)

(

SELECT Items_Purchased as item_category, Order_ID, MONTH(Order_Date) AS order_month, Amount_Paid

FROM Customers

WHERE Order_ID IS NOT NULL

)

SELECT item_category, COUNT(Order_ID) AS "Total Orders Placed",order_month, SUM(Amount_Paid)as "Total Revenue"

FROM Orders_CTE

GROUP BY order_month, item_category

ORDER BY item_category, order_month;

Example of sql server with clause

Multiple With clause in SQL

Example #4

Find out the total revenue generated city-wise by the e-commerce country.

In this illustration, we will be learning to use multiple WITH clauses in a single query.
Code:

WITH Orders_CTE (City,Amount_paid)
AS
(
SELECT City,Amount_Paid
FROM Customers
WHERE Order_ID IS NOT NULL
),
Cities_CTE (City_Name, Country)
AS
(
SELECT City_name, Country
FROM City_Details
)
SELECT c.Country, SUM(o.Amount_paid)as "Total Revenue"
FROM Orders_CTE as o LEFT JOIN Cities_CTE as c
ON o.City =c.City_Name
GROUP BY c.country
ORDER BY 2 DESC;

WITH Orders_CTE (City,Amount_paid)

(

SELECT City,Amount_Paid

FROM Customers

WHERE Order_ID IS NOT NULL

Cities_CTE (City_Name, Country)

(

SELECT City_name, Country

FROM City_Details

)

SELECT c.Country, SUM(o.Amount_paid)as "Total Revenue"

FROM Orders_CTE as o LEFT JOIN Cities_CTE as c

ON o.City =c.City_Name

GROUP BY c.country

ORDER BY 2 DESC;

Multiple With clause in SQL

Conclusion:

As above you can see that SQL WITH clause is used generally for creating temporary tables that can be used further in the main SQL query. Furthermore, it reduces the cost of joining operations and helps in reusing the same or equivalent piece of code again and again by referencing. The SQL WITH clause is very good when used with advanced or complex SQL statements rather than simple ones
It also allows you to break down complex SQL queries into smaller ones that make it simple for debugging & processing the most complex queries. The WITH clause in SQL Server is generally a drop-in replacement to the normal sub-query.

What is the With Clause in SQL Server | SQL WITH Clause Tricks