Introduction to Common Table Expression(CTE) in PostgreSQL

Introduction

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

Straight to example

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

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;

In the above example, we are generating the numbers from 1 to 10.

References

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

Conclusion

In this article we have seen as how to use CTE (Simple or Recursive) in PostgreSQL.Hope this will be helpful.Thanks for reading.