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.

Delegate vs Action vs Func

Traditional way of using Delegate

When we talk about delegate, it is a class of reference type used to encapsulate named or anonymous methods. For creating delegate we write,

1

Now, using the delegate which is declared can be done as shown below5

Now C# had added a small wrapper to the delegate with the help of Action<in T> delegate.


Action<T>

This delegate was introduced in Framework 2.0. We can now use Action<in T> delegate to pass a method as a parameter without explicitly defining the delegate. The compiler will take care of it. This delegate can accept parameters but without a return type.

3

Call the delegate using act(4, 5)


Func< T, TResult>

This was introduced in Framework3.5. This delegate is different from Action<T> in the sense that it supports for return value.

4

To call this delegate we can use

Console.WriteLine(“Using Func<> :” + fn(6, 6));
As it is returning the value. Hope you got the difference.