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),
(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
WHERE EmployeeName = 'Nandy'
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.