CTE is an abbreviation Common Table Expression. A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.
Advantage
1. Using CTE improves the readability and makes maintenance of complex queries easy.
2. The query can be divided into separate, simple, logical building blocks which can be then used to build more complex CTEs until final result set is generated.
3. CTE can be defined in functions, stored procedures, triggers or even views.
4. After a CTE is defined, it can be used as a Table or a View and can SELECT, INSERT, UPDATE or DELETE Data.