CTE (Common Table Expression) in SQL

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

Syntax:
WITH name[(columnname)]
AS (query)

Let’s take example of Customer and Order tables having parent child relationship as shown below-

1

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

Multiple expression
We can also have multiple expressions defined separated by comma as shown below

WITH tbl1 AS
(SELECT * FROM Table1),
tbl2 AS
(SELECT * FROM Table2)

Recursive CTE
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.

3

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;

Output:

4

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.

Advertisements