CTE is virtually created temporary storage which holds query output created by SQL SYNTAX SELECT, INSERT, UPDATE, DELETE OR CREATE VIEW STATEMENT. It holds the data until the time of the query session. It is similar to the derived table only. We can also  use the CTE in case of VIEW in some scenarios:

• expression_name: Is a valid identifier for the common table expression. It must be different from the others defined within the same WITH clause, but it can be the same as the name of a base table or view. Any REFERENCE to it in the query uses the common table expression and not the base object
• column_name: Specifies a unique column name in the common table expression. The number of column names specified must match the number of columns in CTE _query_definition

If the query definition supplies distinct names for all columns then the column names are optional

• CTE_query_definition: This is a SELECT statement whose result set populates the common table expression. This must meet the same requirements as for creating a view except that a CTE cannot define another CTE

Let me LIST down some benefits of using CTE :

• When we have a recursive query we can use CTE. It holds a query output in an area that is defined during CTE definition. It is more beneficial when we need to work on the queries query. It holds query data till the time the SQL query is running. It is beneficial for holding filter data which is needed for subsequent processing.
• It improves the readability of queries without affecting performance.
• It can be referenced multiple times in the SQL query.
• It can be used instead of a view where metadata information does not require to be stored.

There are two types of CTE :

• Recursive CTE: This type of reference itself within CTE. This is useful while working with hierarchical data SINCE it executes until the time the whole hierarchy gets returned. A recursive query must contain two SQL query statements joined by UNION ALL, UNION, INTERSECT, or EXCEPT operator. In the below example we have Employee table having hierarchical data (Employee and Manager relationship)

• Non-Recursive CTE: This type does not reference itself. This is much simpler than other types of CTE.