Common Table Expressions

Common Table Expressions are also called CTEs. This feature was introduced with SQL Server 2005. The CTE is preferred to use as an alternative to a Subquery/View.

Sub-queries

A sub-query is a query within a query. It is also called an inner query or a nested query. A sub-query is usually added in a where clause of the SQL statement.

Example
Select Name,Age, employeeID   
From table1   
Where employeeID in   
( 
   Select employeeID from table2 where salary >=7500 /******Sub Query******/ 
)

use a CTE

With aliastablename (column1) 
 
AS 
 
(Query)

example
With table1CTE(EmployeeID) 
 
AS   
(Select employeeID from table1 where salary >=7500)

Advantages
CTE improves the code readability.
CTE provides recursive programming.
CTE makes code maintainability easier.
Though it provides similar functionality as a view, it will not store the definition in metadata.