Different types of Common Table expression

Question

I study CTE in depth. So, I would like to know what are the different types in CTE in SQL?

Answer 1

--Type 1: Using as a sub query ;WITH CTE1 AS ( SELECT EmployeeId, EmployeeName FROM Employees ) SELECT * FROM CTE1 --Type 2: Using as a recursive query (ex; employee manager situation or node structure) ;WITH CTE3 AS ( SELECT EmployeeId, ManagerId FROM Employees WHERE EmployeeId = @EmpId UNION ALL SELECT e1.EmployeeId, e1.ManagerId FROM Employees e1 JOIN CTE3 ON e1.ManagerId = CTE3.EmployeeId ) SELECT EmployeeId, ManagerId FROM CTE3 --This is a slightly different syntax (not a logical difference) --where returning column names are specified within brackets ;WITH CTE2 (EmployeeId, EmployeeName) AS ( SELECT EmployeeId, EmployeeName FROM Employees ) SELECT * FROM CTE2