Explain CTE (Common Table expression) with example?

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).