1. Common Table Expressions
Common Table Expressions, commonly known as CTEs are another type of derived table. They are little different as they can be used multiple times in a query and are defined like a table.
2. CTE syntax
A CTE acts a lot like a table which is defined before you use it. Once you define the CTE, then you can use it in the query following the CTE, as if it was a table.
When creating a CTE, you specify the keyword WITH, followed by the CTE name, then a list of columns it contains. Then, specify the keyword AS followed by the query used to create a CTE within parentheses. The column names need to match the columns in the query. Let's look at an actual example now.
3. CTEs in T-SQL
This CTE is named BloodPressureAge and it contains two columns, Age and MaxBloodPressure. After the columns are listed, the keyword AS is used, and the query to create the CTE is specified inside the parentheses. This first query contains the age and the max BloodPressure grouped by age. Notice that the query and the CTE definition both contain two columns.
Once the CTE has been defined, it can be used like any table in the query below it. The query after the CTE uses the CTE BloodPressureAge as if it was a table. The query joins the bloodpressureAged to the CTE show both the minimum and maximum BloodPressure based upon Age.
4. Let's practice!
Now let's try some example CTEs.