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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- 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; |
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.
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:
1 2 3 4 5 6 7 8 9 |
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; |
Example #2
Find out the total number of orders placed per month for every category item sold at the e-commerce website.
Code:
1 2 3 4 5 6 7 8 9 10 11 |
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; |
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:
1 2 3 4 5 6 7 8 9 10 11 |
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; |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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; |
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.