Common Table Expression

When we use the same set of a particular data multiple times in between the entire script, it make sense to put that in common table expression to use instead of the query.


Simple Example for CTE.


WITH [CTE_name] AS (SELECT EmpId, designation FROM work.dbo.employee)
SELECT RepId
 ,ProductId
 ,ProductName
 ,ProductType
 ,Price
 ,C.Designation 
FROM work.dbo.Product P
 JOIN CTE_Name C 
   ON C.empid = P.RepId