Common Table Expression(CTE) is particularly useful when subquery is executed multiple times. It is equally helpful in place of temporary tables.It computes the aggregation once, and allows us to reference it by its name (may be multiple times) in the queries.It materialize subqueries thereby helping oracle not to recompute them multiple times.In PostgreSQL, we use the WITH clause for writing CTE based queries.It helps in breaking down complicated and large queries into simpler forms which is easily readable.

Let us see how we can do so

Simple CTE

With CTE AS
(	
  Select 
	empid
	, empname
	, salary
	, belongsto
	, deptid
  FROM tblemployee

)
Select * From CTE;

One thing to notice here is that in Sql Server we start with a semicolon(;) before writing a CTE inorder to differentiate it from other statements previously written.

This is a simple CTE where basically we are projecting the employee table records

Multiple CTE or Chain CTE

With CTEEmp AS
(	
  Select 
	empid
	, empname
	, salary
	, belongsto
	, deptid
  FROM tblemployee
)
,CTEDept As
(
  Select e.*,d.deptname
  From CTEEmp e
  Join tbldept d
  On e.deptid = d.deptid
)
Select * From CTEDept;

It is call as CTE chains/multiple CTEs.We can find that we are using the resultset of the first CTE (CTEEmp) into the second one(CTEDept) to obtain the result

Recursive CTE or Hierarchial queries

It is the third form of CTE where a CTE can reference to itself. So we call it as recursive CTE. In PostgreSQL, we can achieve so by using the Recursive keyword in the CTE which ensures that the query can refer to its own output

In the next example we will look into how we can generate a number table by the help of Recursive CTE

With Recursive CTE(Rn) AS
(	
	Select 1
	Union All
	Select Rn + 1 From CTE
	Where Rn < 10
)
Select * From CTE;

In Sql Server , CTE was introduce since version 2005. It is available in all the three flavours described here. But for using a recursive CTE, we donot use "Recursive" keyword. We will write the same program for generating a number table using recursive CTE using SQL Serevr CTE syntax below

;With CTE AS
(	
	Select Rn = 1
	Union All
	Select Rn + 1 From CTE
	Where Rn < 10
)
Select * From CTE;

Reference(s)

1. WITH Queries (Common Table Expressions)
2. CTEReadme

Hope this helps

Thanks for reading