CTE - Common table expression

CTE - Common table expression

It is difficult to write and maintain queries that has number of joins and very complex structure. These queries can made easier to write, read and maintain by the help of CTE.

CTE is common table expression. It is a temporary result set which allow you to define subquery by assigning a alias name and use it further as a temporary table. You can create as many as CTE alias result set and use it to display output.

Example -

select * from(select id, name, class from student) S
where S.class > 6 order by S.id

The query looks bit complex and difficult to understand. But by using CTE. We can create a temporary result set which is easy to understand and maintain. And can used at time to display output.

With T(id, name, class)
As
(
select id, name, class from student
)
select * from T where class > 6

Here you can see above CTE is very simple, easy to understand and maintain.

Syntax -

1) WITH keyword is followed by the CTE name.
2) Column list in parenthesis is optional.
3) Query is written after AS keyword in parenthesis.

A CTE can be used to:

• Create a recursive query. 
• Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
• Reference the resulting table multiple times in the same statement.