MySQL CTE (WITH clause)

MySQL CTE (WITH clause) is a SQL statement in the MySQL Specific category. MySQL 8.0+: support for Common Table Expressions including recursive CTEs for hierarchical data. The syntax is WITH cte_name AS (SELECT ...) SELECT * FROM cte_name;. It returns cTE result. A typical example: WITH high_earners AS ( SELECT name, salary, department FROM employees WHERE salary > 80000 ) SELECT department, AVG(salary) AS avg_sal, COUNT(*) AS count FROM high_earners GROUP BY department; -- Recursive CTE in MySQL 8.0: WITH RECURSIVE org_chain AS ( SELECT id, name, manager_id, 1 AS depth FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, oc.depth + 1 FROM employees e JOIN org_chain oc ON e.manager_id =… A close relative is AUTO_INCREMENT, which mySQL auto-generates sequential integer values for this column. Commonly used with PRIMARY KEY. A close relative is ENGINE, which specifies the MySQL storage engine. InnoDB (default) supports transactions and foreign keys. MyISAM is faster for read-heavy.