Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It helps organize complex queries and improves readability.

Writing a CTE

A CTE begins with the WITH keyword, followed by a name and the query inside parentheses.

Example:

WITH avg_age AS 
(SELECT AVG(age) AS average FROM students)

This defines a CTE named avg_age that calculates the average student age.

Using a CTE

You can then use the CTE like a regular table in your main query:

SELECT name, age 
FROM students, avg_age
WHERE students.age > avg_age.average;

This selects students older than the average age calculated in the CTE.

CTEs are especially useful for breaking down complex queries into simpler parts.

‍