Cte

CTE (COMMON TABLE EXPRESSION) :

A CTE is a temporary result set, that can be refferenced with in a SELECT,UPDATE,DELETE statements that immediately follows the CTE.

with CTEName 
as
(
---select statements
)

with employeecount as
(
    select dept_no,count(*) as totalemployees  from emp 
)

select dname,totalemployees from dept
inner join employeecount on dept.deptno=employeecount.dept_no

RECURSIVE CTE :

A CTE that references it self is called Recursive CTE.

ex:

with cte_emp 
as (
    select empno,ename,mgr,job,1 as [level] 
    from emp
    where mgr is null

    unoin all 

    select e.empno,e.ename,e.mgr,e.job,ce.[level] + 1
    from emp e 
    inner join cte_emp ce on e.mgr=ce.mgr

)

select ce.ename,ISULL(cast(cm.mgr as varchar(50)),'SUPER BOSS')
from cte_emp ce
left join  cte_emp cm