Common table expressions (CTEs) allow you to break down complex SQL logic into smaller pieces.Common table expressions (CTEs) are simply defined using the WITH keyword. They allow for logic to be coded in a single place and simplify coding and maintainability.

CTE is like a temporary view.

CTE Syntax:

  • A CTE must be part of a SELECT statement.
  • A CTE is defined using the WITH keyword.
  • The WITH keyword can define one or more CTEs.

Example :

With Temp As

(

Select  *  From Custable

Where Customer_City = ‘CHICAGO’

Order By Customer_Name

)

Select * from Temp