Common Table expression:
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:
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).
