Common Table Expression (CTE)

What is a CTE?

A CTE (Common Table Expression) is a temporary result set defined using the WITH clause.
It can be referenced like a table in your main query.

CTEs make SQL:

• Easier to read

• Modular (reuse logic)

• Helpful for recursive queries, too

---

✅ Basic Syntax

WITH cte_name AS (
    SELECT ... FROM ...
)
SELECT * FROM cte_name;

____________________________________________________________________________

Find employees earning above the average salary.

✅ Query Using CTE:

WITH avg_salary_cte AS (
    SELECT AVG(salary) AS avg_salary FROM employees
)
SELECT e.emp_id, e.emp_name, e.salary
FROM employees e, avg_salary_cte a
WHERE e.salary > a.avg_salary;