Common Table Expressions (CTE) in SQL SERVER
1. Common Table Expressions are similar to that of a Derived Table.
2. We can define a CTE and use it multiple times in a query unlike Derived Tables.
3. The number of columns in the expression should be similar to that of the query result used in CTE.
4. CTE can be used recursively also.
The Syntax is as follows
WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
The below example demonstrate the use
WITH Dt_CTE (DeptID, DeptName, Status)
AS
(Select * from Dept where Status !='InActive')
Select EmployeeID, FirstName, LastName, Dt.DeptName from Employee INNER JOIN Dt_CTE Dt ON Dt.DeptID = Employee.DeptID
2. We can define a CTE and use it multiple times in a query unlike Derived Tables.
3. The number of columns in the expression should be similar to that of the query result used in CTE.
4. CTE can be used recursively also.
The Syntax is as follows
WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
The below example demonstrate the use
WITH Dt_CTE (DeptID, DeptName, Status)
AS
(Select * from Dept where Status !='InActive')
Select EmployeeID, FirstName, LastName, Dt.DeptName from Employee INNER JOIN Dt_CTE Dt ON Dt.DeptID = Employee.DeptID
Comments