Common Table Expressions in Sql Server

Common Table Expressions(CTE) is used when you are working with complex queries. Your requirement is to fetch specific data from tables that is returned after multiple operations like sub query or multiple joints then CTE is useful for you.



Here I am giving you example how to use CTE

Lets You want to fetch records of Employee which Organization name is xyz.

SELECT * FROM  (
        SELECT O.OrgName, E.Name, E.Address From Organization O
        Inner join Employee E on E.OrgId= O.OrgId) tbl
WHERE tbl.OrgName ='xyz'
ORDER BY T.NAME

In first look the query looks complex. It can be simplify by CTE as.

;With Emp(OrgName, Name,Address)  --Column names for Temporary table
AS
(
SELECT O.OrgName, E.Name, E.Address,E.Address From Organization O
Inner join Employee E on E.OrgId= O.OrgId
)
SELECT * FROM Emp  --SELECT for CTE temporary Table
WHERE Emp.OrgName ='xyz'
ORDER BY Emp.NAME

Scope of CTE

;With Emp(OrgName, Name,Address)  --Column names for Temporary table
AS
(
SELECT O.OrgName, E.Name, E.Address,E.Address From Organization O
Inner join Employee E on E.OrgId= O.OrgId
)
SELECT * FROM Organization;
SELECT * FROM Emp  --SELECT for CTE temporary Table
WHERE Emp.OrgName ='xyz'
ORDER BY Emp.NAME ;

Here CTE scope upto Organization.