SQL · Pattern 9 — Common Table Expressions (CTEs)
Basic CTE
A Common Table Expression (CTE) is a temporary named result set created using the WITH clause.
Source data — Pattern 9: Common Table Expressions (CTEs)
All queries run against the shared employees and departments tables (see schema.sql). This pattern introduces Common Table Expressions (CTEs). Topic 54 demonstrates a recursive CTE using the employee-manager hierarchy from the employees table.
What it does
A Common Table Expression (CTE) is a temporary named result set created using the WITH clause. It improves readability and simplifies complex queries.
Problem
Display employees earning above average.
WITH avg_salary AS (
SELECT AVG(salary) avg_sal FROM employees
)
SELECT first_name,salary
FROM employees,avg_salary
WHERE salary>avg_sal;
Result
