When dealing with sub-queries, it is often required that you need to select a part of the data from a sub query or even join data from a query with some other tables. In that case, either you have an option to name your sub-queries with an alias or to use it directly. Gradually your requirement is getting more and more complex and your query would look unmaintainable at any time. CTE allows you to define the subquery at once, name it using an alias and later call the same data using the alias just like what you do with a normal table. CTE is standard ANSI SQL standard.

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW 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. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.The basic syntax structure for a CTE is: WITH expression_name [ ( column_name ) ] AS ( CTE_query_definition )

Its Common Table Expression where we can gather data from multiple tables or from joins and can be treated as a Common Table.

http://www.codeproject.com/Articles/275645/CTE-In-SQL-Server

CTE stands for Common Table expressions 1.This is used to store result of a complex sub query for further use. 2.This is also used to create a recursive query.

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 statemnt. 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.