How to use CTE's

What is a CTE?

Basically a common table expression is a table that gets created and only lasts for the duration of that query. Once the query is executed, the table doesn't exist anymore. It can be self referenced and can be referenced multiple times in the same query.

The syntax

The syntax of a CTE is as follows

WITH CTE-NAME(optional-distinct-name-1, optional-distinct-name-2, optional-distinct-name-3) 

AS

( place your SQL code here to select your columns)

The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

Then to run the CTE and use it, you do the following:

SELECT <column-name-1, column-name-2 ,column-name-3>

FROM CTE-NAME;

Why CTE's?

• CTE's can be used to create recursive queries. 
• Substitute views when you don't really need to store a view
• Reference the same table multiple times in a statement
• Enable grouping in certain cases

It can also be used instead of an inner select and it make your query more readable than using an inner SELECT.