CTE and WITH Clause

CTEs with WITH Clause

CTEs help structure queries by assigning temporary names to subqueries. Useful for reuse and readability.

WITH dept_avg AS (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department_id
)
SELECT first_name, salary, avg_sal
FROM employees e
JOIN dept_avg d ON e.department_id = d.department_id
WHERE e.salary > d.avg_sal;