Common Table Expression(CTE)
- Common Table Expression is the temporary result set, and it should be with in the execution of the one SELECT, DELETE, UPDATE, INSERT or Create View statement.
- It will work during query execution only.
- This will store as an object in the SQL Server.
- CTE is the self referencing and could be able to refer multiple time in the same query.
WITH Expression_Name [ ( ColumnName [1,...n] ) ] AS ( CTE query definition )
Usage of CTE:
- We can create a recursive query.
- CTE will be the substitute of VIEW. no need to store definition in the metadata.
- This is enable grouping by a column CTE will derived from the scalar subselect.
- Need to refer the same table multiple times in the same query.
- Readability will increase and maintenance of the complex queries very easily.
- This one of the best SQL Server Interview Question.
Examples of CTE:
Before going to run below queries please run input table script.
--example one with EmployeeDept_CTE as( select e.eno,e.ename,ed.dno from employeeMaster e full outer join empDeptMapping ed on e.eno=ed.eno) select * from EmployeeDept_CTE --example two with EmployeeDept_CTE([Employee Number],[Employee Name],[Dept No]) as( select e.eno,e.ename,ed.dno from employeeMaster e full outer join empDeptMapping ed on e.eno=ed.eno) select * from EmployeeDept_CTE
WE have to use the CTE name quickly, after defining the CTE. like
In this example, we have to use below line after define EmployeeDept_CTE
select * from EmployeeDept_CTE other wise we will get below error.
Msg 208, Level 16, State 1, Line 7 Invalid object name 'EmployeeDept_CTE'.
In the above example one we have used EmployeeDept_CTE with out declaring column name.
In the above example two we have used EmployeeDept_CTE with declaring column name.
Both the output will same, but in the output column names will display in the second query.
Output for second query:
|Employee Number||Employee Name||Dept No|