CTE
stands for Common Table expressions. It was introduced with SQL Server 2005. It
is a temporary result set which can be referenced in the same query just
as a view. Unlike temporary table its life is limited to the current query. It
is defined by using WITH statement. CTE improves readability and ease in
maintenance of complex queries and sub-queries.
Syntax:
; WITH CTEName (Column aliases)
AS (Subquery)
SELECT statement
FROM CTEName;
Example:
Complex
query Using sub-query:
SELECT * FROM (
SELECT Dept.Department,
Emp.Name, Emp.Age From Department
Dept
Inner join Employee Emp on Emp.EID = Dept.EID)
Temp
WHERE Temp.Age >
40
ORDER BY Temp.NAME
By
Using CTE:
;With CTE1(Department, Name, Age)--Column names
AS
(
SELECT Dept.Department,
Emp.Name, Emp.Age from Department
Dept
INNER JOIN EMP Emp ON
Emp.EID = Dept.EID
)
SELECT * FROM
CTE1 --Using CTE
WHERE CTE1.Age >
40
ORDER BY CTE1.NAME
When to Use CTE
1. This is a substitute of
complex sub query.
2. Substitute for a view
when the general use of a view is not required.
3. This is also used to
create a recursive query or non-recursive query.
4. We can also update data
using CTE (Common table expression).
