Using CTE in sql

CTE in SQL

CTE is abbreviated as Common Table Expression.

CTE holds the query results temporarily later this results set can be manipulated for only next instance. 

Which means CTE is always followed by any DML i.e., Select,Insert,Update,Delete.
Only till the execution of the DML CTE will be able to hold the data (query results).

Please, view the below mentioned sample  with syntax

with cte as
(
    Select * from Table1 join Table2 on Table1.ColumnName=Table2.ColumnName
) 
Select * from cte

Pass CTE results to other CTE 

CTE data can be hold and passed to another CTE 

Example

with cte as
(
    Select * from Table1 join Table2 on Table1.ColumnName=Table2.ColumnName
) ,

cte2
(
    Select * from Table3 join cte on Table3.ColumnName=cte.ColumnName
) 
select * from cte2