A Common Table Expression (CTE) is a named temporary result set in MySQL that exists only during the execution of a single SQL statement. CTEs are mainly used to simplify complex queries, replace deeply nested subqueries, and improve query readability.
The CTE is written before the main SELECT, INSERT, UPDATE, or DELETE statement. Once defined, it can be referenced multiple times within the same query.
-- Basic structure of a CTE in MySQL
WITH cte_name AS (
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT * FROM cte_name;
-- Select employees earning more than 50,000 using CTE
WITH high_salary_employees AS (
SELECT id, name, salary
FROM employees
WHERE salary > 50000
)
SELECT id, name, salary
FROM high_salary_employees;
-- Calculate average salary per department using CTE
WITH department_avg_salary AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT department_id, avg_salary
FROM department_avg_salary;
-- Use CTE with JOIN to compare employee salary with department average
WITH dept_avg AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d
ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;