Different types of Common Table Expression

Question

I am learning CTE in depth. So I would like know what are the different types in CTE's 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