A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
In simple layman's terms, CTE basically provides data at the runtime which may come from multiple tables. For e.g. if we have different tables like emp, dept, salgrade etc. then CTE can help in getting the resultset/dataset from multiple tables and form a temporary table. Then we can refer this table within other query any number of times.
For e.g. if we want to display the following:
Now in the above example there are two tables used i.e.
a. Emp - which stores the information about the employees with their deptno.
b. Dept - which acts as the master of the Department information
The 'total count' column is generated from the 'Emp' table with the help of CTE and then referred in the other query to display dept wise information. The following is the syntax of the CTE that is used to generate the result as mentioned above.
With TotalNoOfEmployeesDeptWise(Deptno, TotalCount) as
(
Select Deptno, COUNT(*) from Emp group by Deptno
)
Select Dept.Deptno, Dept.Dname, Dept.Location, x.TotalCount from Dept, TotalNoOfEmployeesDeptWise x
where X.Deptno=Dept.Deptno
In simple layman's terms, CTE basically provides data at the runtime which may come from multiple tables. For e.g. if we have different tables like emp, dept, salgrade etc. then CTE can help in getting the resultset/dataset from multiple tables and form a temporary table. Then we can refer this table within other query any number of times.
For e.g. if we want to display the following:
Deptno | Dname | Location | TotalCount |
10 | Hotel Management | Amritsar | 6 |
20 | Information Technology | Chicago | 1 |
30 | Finance | Ludhiana | 2 |
Now in the above example there are two tables used i.e.
a. Emp - which stores the information about the employees with their deptno.
b. Dept - which acts as the master of the Department information
The 'total count' column is generated from the 'Emp' table with the help of CTE and then referred in the other query to display dept wise information. The following is the syntax of the CTE that is used to generate the result as mentioned above.
With TotalNoOfEmployeesDeptWise(Deptno, TotalCount) as
(
Select Deptno, COUNT(*) from Emp group by Deptno
)
Select Dept.Deptno, Dept.Dname, Dept.Location, x.TotalCount from Dept, TotalNoOfEmployeesDeptWise x
where X.Deptno=Dept.Deptno
So as you can see in the code, the first line, starting with 'With', the CTE starts and generates a runtime dataset.
And 'Select' is actual query that is referring to the CTE for displaying information.
Comments