What is the use of CTE or Common Table Expression?

Answer:

We can think a Common Table Expression Or CTE as a temporary table.A CTE is just like a derived table because CTE is not stored as an object and exists only for the duration of the query.In other words,a CTE is similar to VIEW also.

We can define or create a CTE as an expression,optional column list and a query.We have to specify WITH keyword and Semicolon(;) as prefix but Semicolon is optional.

Syntax:-

With CTE_As_Table(col1,col2....)

 AS

(Select Statement)

Here,CTE_As_Table will be treated as Table name as

Select * From CTE_As_Table;