Common table expressions CTE - SQL Server

Common table expression is a similar concept of a derived table in the sense that it’s a named table expression that is visible only to the statement .

A derived table is basically a subquery, except it is always in the FROM clause of a SQL statement. The reason it is called a derived table is because it essentially functions as a table as far as the entire query is concerned.

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.
A CTE can be used to:
Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
Reference the resulting table multiple times in the same statement.




Common Table Expression (CTE)  contains  three parts:

a) The name Starts  WITH keyword
b) List of Columns (optional)
c) Query (in parentheses after the AS keyword)

The query using the CTE must be the first query appearing after the CTE.

You have a query like this

SELECT * FROM (
SELECT A.productid, A.productname, E.categoryname,a.unitprice From Production.Products A Inner join Production.Categories E on E.categoryid = A.categoryid) T
WHERE T.unitprice > 50
ORDER BY T.categoryname

rewriting the query using CTE expression as follows

With T(productid, productname,categoryname, unitprice)
AS
(
SELECT P.productid, P.productname, C.categoryname,P.unitprice From Production.Products P
Inner join Production.Categories C on C.categoryid = P.categoryid
)
SELECT * FROM T
WHERE T.unitprice > 50
ORDER BY T.categoryname