A CTE stands for common table expressions and is a named subquery normally defined with a WITH course.
A CTE exists temporarily and is used within the context of a larger query. They are useful because:
- Simplify complex queries by organising them into logical parts
- Can reference a CTE multiple times within the same query
- They're not stored in the database and only exists during the execution of the query
Syntax
WITH cte_name AS (
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT *
FROM cte_name;
IMPORTANT TO NOTE: with clause is processed before the main query execution begins as it acts as a preparatory step. It therefore ensures the CTE can be used and referred to in the main query.