WITH Clause in SQL Server

WITH Clause in SQL Server

WITH Clause in SQL Server

In this post I will explain all about WITH Clause in SQL Server end to end with appropriate examples, So let’s start –

WITH Clause in SQL Server

WITH clause is similar to a view only the difference it is not stored in the database schema as the view has a name in the database schema so that other queries can use it like a table or virtual table. It is only valid in the query where it was used. It makes it possible to improve the structure of a statement without polluting the global namespace. Microsoft added WITH clause in year 1999 to define ‘Statement scoped views’. WITH clause also known as CTE(Common Table Expression) and subquery factoring. In other words we say that SQL ‘With’ clause treats as a table of query results.

WITH Clause in SQL Server

SQL WITH clause allows us to give a subquery block a name(a process also called subquery refactoring) which can be referenced in several places within the main query.

Rules of WITH Clause in SQL Server

  1. WITH clause is used for defining a temporary relation such that the output of this temporary relation is available and is used by the query is associated with ‘WITH‘ clause
  2. WITH clause is not supported by all the database systems
  3. Name assigned to the subquery is treated as though it was an inline view or table

Syntax:

WITH <alias_name_A> AS (sql_subquery_statement),

<alias_name_B> AS(sql_subquery_statement_from_alias_name_A

or sql_subquery_statement )

SELECT <column_list>

FROM <alias_name_A>, <alias_name_B> [,table_names]

[WHERE <join_condition>]

The syntax after the keyword WITH is the same as it is for CREATE VIEW it starts with the query name and in parenthesis the name of the columns it returns. The keyword AS introduces the definition query itself. WITH is not a stand-alone command like CREATE VIEW is, it must be followed by a SELECT statement within this query, and subqueries contain can refer to the just defined query name in their FROM clause.

WITH clause can introduce multiple queries by separating them with a comma but not repeated WITH keyword. let’s see an example –

If we have an employees table in our database, in which we want to find all the employees whose salary is more than the average salary of all employees. In my case, there is one table Employees in which total 107 employees are there with different salaries as follows –

Employee_IDFirst_NameEmailSalary
100StevenSKING24000
101NeenaNKOCHHAR17000
102LexLDEHAAN17000
103AlexanderAHUNOLD9000
104BruceBERNST6000
105DavidDAUSTIN4800
106ValliVPATABAL4800
107DianaDLORENTZ4200
108NancyNGREENBE12008
109DanielDFAVIET9000
110JohnJCHEN8200
111IsmaelISCIARRA7700
112Jose ManuelJMURMAN7800
113LuisLPOPP6900
114DenDRAPHEAL11000
115AlexanderAKHOO3100
116ShelliSBAIDA2900
117SigalSTOBIAS2800
118GuyGHIMURO2600
119KarenKCOLMENA2500
120MatthewMWEISS8000
121AdamAFRIPP8200
122PayamPKAUFLIN7900
123ShantaSVOLLMAN6500
124KevinKMOURGOS5800
125JuliaJNAYER3200
126IreneIMIKKILI2700
127JamesJLANDRY2400
128StevenSMARKLE2200
129LauraLBISSOT3300
130MozheMATKINSO2800
131JamesJAMRLOW2500
132TJTJOLSON2100
133JasonJMALLIN3300
134MichaelMROGERS2900
135KiKGEE2400
136HazelHPHILTAN2200
137RenskeRLADWIG3600
138StephenSSTILES3200
139JohnJSEO2700
140JoshuaJPATEL2500
141TrennaTRAJS3500
142CurtisCDAVIES3100
143RandallRMATOS2600
144PeterPVARGAS2500
145JohnJRUSSEL14000
146KarenKPARTNER13500
147AlbertoAERRAZUR12000
148GeraldGCAMBRAU11000
149EleniEZLOTKEY10500
150PeterPTUCKER10000
151DavidDBERNSTE9500
152PeterPHALL9000
153ChristopherCOLSEN8000
154NanetteNCAMBRAU7500
155OliverOTUVAULT7000
156JanetteJKING10000
157PatrickPSULLY9500
158AllanAMCEWEN9000
159LindseyLSMITH8000
160LouiseLDORAN7500
161SarathSSEWALL7000
162ClaraCVISHNEY10500
163DanielleDGREENE9500
164MatteaMMARVINS7200
165DavidDLEE6800
166SundarSANDE6400
167AmitABANDA6200
168LisaLOZER11500
169HarrisonHBLOOM10000
170TaylerTFOX9600
171WilliamWSMITH7400
172ElizabethEBATES7300
173SunditaSKUMAR6100
174EllenEABEL11000
175AlyssaAHUTTON8800
176JonathonJTAYLOR8600
177JackJLIVINGS8400
178NULLKGRANT7000
179CharlesCJOHNSON6200
180WinstonWTAYLOR3200
181JeanJFLEAUR3100
182MarthaMSULLIVA2500
183GirardGGEONI2800
184NanditaNSARCHAN4200
185AlexisABULL4100
186JuliaJDELLING3400
187AnthonyACABRIO3000
188KellyKCHUNG3800
189JenniferJDILLY3600
190TimothyTGATES2900
191RandallRPERKINS2500
192SarahSBELL4000
193BritneyBEVERETT3900
194SamuelSMCCAIN3200
195VanceVJONES2800
196AlanaAWALSH3100
197KevinKFEENEY3000
198DonaldDOCONNEL2600
199DouglasDGRANT2600
200JenniferJWHALEN4400
201MichaelMHARTSTE13000
202PatPFAY6000
203SusanSMAVRIS6500
204HermannHBAER10000
205ShelleySHIGGINS12008
206WilliamWGIETZ8300

Now we will write a query to find all the employees whose salary is more than the average salary of all employees as mentioned below –

WITH
T1 (AvgSalary) AS (SELECT AVG(Salary) FROM Employees),
T2 AS (SELECT Employee_ID,First_Name,Email, Salary FROM Employees)

SELECT T2.Employee_ID,T2.First_Name,T2.Email,T2.Salary,T1.AvgSalary
FROM T2, T1 WHERE T2.salary>T1.AvgSalary

Now we will see the result as shown below –

Employee_IDFirst_NameEmailSalaryAvgSalary
100StevenSKING240006461.8317
101NeenaNKOCHHAR170006461.8317
102LexLDEHAAN170006461.8317
103AlexanderAHUNOLD90006461.8317
108NancyNGREENBE120086461.8317
109DanielDFAVIET90006461.8317
110JohnJCHEN82006461.8317
111IsmaelISCIARRA77006461.8317
112Jose ManuelJMURMAN78006461.8317
113LuisLPOPP69006461.8317
114DenDRAPHEAL110006461.8317
120MatthewMWEISS80006461.8317
121AdamAFRIPP82006461.8317
122PayamPKAUFLIN79006461.8317
123ShantaSVOLLMAN65006461.8317
145JohnJRUSSEL140006461.8317
146KarenKPARTNER135006461.8317
147AlbertoAERRAZUR120006461.8317
148GeraldGCAMBRAU110006461.8317
149EleniEZLOTKEY105006461.8317
150PeterPTUCKER100006461.8317
151DavidDBERNSTE95006461.8317
152PeterPHALL90006461.8317
153ChristopherCOLSEN80006461.8317
154NanetteNCAMBRAU75006461.8317
155OliverOTUVAULT70006461.8317
156JanetteJKING100006461.8317
157PatrickPSULLY95006461.8317
158AllanAMCEWEN90006461.8317
159LindseyLSMITH80006461.8317
160LouiseLDORAN75006461.8317
161SarathSSEWALL70006461.8317
162ClaraCVISHNEY105006461.8317
163DanielleDGREENE95006461.8317
164MatteaMMARVINS72006461.8317
165DavidDLEE68006461.8317
168LisaLOZER115006461.8317
169HarrisonHBLOOM100006461.8317
170TaylerTFOX96006461.8317
171WilliamWSMITH74006461.8317
172ElizabethEBATES73006461.8317
174EllenEABEL110006461.8317
175AlyssaAHUTTON88006461.8317
176JonathonJTAYLOR86006461.8317
177JackJLIVINGS84006461.8317
178NULLKGRANT70006461.8317
201MichaelMHARTSTE130006461.8317
203SusanSMAVRIS65006461.8317
204HermannHBAER100006461.8317
205ShelleySHIGGINS120086461.8317
206WilliamWGIETZ83006461.8317

As we can see the average salary of all employees is 6461.8317, therefore all employees whose salary is more than the obtained average displayed in the result set.

Assuming this tutorial post has well-acquainted aspiring to the complete requirement.

Also requesting, if you have any ideas or suggestions for me or you need some important blogs/Videos or guidelines related to SQL Server, Excel, WordPress, SEO, Website technologies then please feel free to write me on below comment section or you can go through the Contact Page. I will definitely come back to you with your desired requirements.

Hope this tutorial post was helpful for you and you liked very well, I am happy to serve you better. If you liked really this tutorial post, please don’t forget to share on social media like – Facebook, Whatsapp, LinkedIn, Twitter, Instagram, etc as it may help some other needy peoples too.

Leave a Reply