Common Table Expression (CTE) can also be said as the derived table or view. Lifetime of a CTE is ended after a single execution of query.CTE is mainly used for
- Code readability
- Breaking complex queries in to smaller blocks
- They are not persistent as database object
Let’s take example of Customer and Order tables having parent child relationship as shown below-
Create Common table expression as shown below-
WITH tbl AS (SELECT CustomerID, OrderNo, Product FROM Orders o INNER JOIN OrderDetails od ON o.OrderID = od.OrderID) SELECT * FROM Customer c INNER JOIN tbl t ON c.CustomerID = t.CustomerID
We can also have multiple expressions defined separated by comma as shown below
WITH tbl1 AS (SELECT * FROM Table1), tbl2 AS (SELECT * FROM Table2)
CTE provides the significant advantage of being able to reference itself, so we can have recursive query execution.
Let’s take example of Employee where I need to find the respective reporting manager of Nandy.
WITH empCTE AS ( SELECT ManagerId, EmployeeName, EmployeeId FROM Employee WHERE EmployeeName = 'Nandy' UNION ALL SELECT mgr.MAnagerId, mgr.EmployeeName, mgr.EmployeeId FROM empCTE usr INNER JOIN Employee mgr ON usr.ManagerId = mgr.EmployeeId WHERE usr.ManagerID IS NOT NULL ) SELECT * FROM empCTE;
CTE with DML statement
WITH tblCTE AS (SELECT Column1 FROM Table1) INSERT INTO Table2(Col1) SELECT Column1 FROM tblCTE;
Hope this helps you at some point of time.