What is a Common Table Expression (CTE) and what are its advantages?

Question

Accepted Answer

48
   What is a Common Table Expression (CTE) and what are its advantages?
  Level: Advanced — A modern, readable alternative to derived tables and complex subqueries.

  A Common Table Expression (CTE) is a temporary, named result set defined using the WITH keyword, which you can reference within a single SELECT, INSERT, UPDATE, or DELETE statement. It exists only for the duration of that query.

  
     Prerequisite: CTEs require MySQL 8.0 or later. They are not available in MySQL 5.7 or earlier — a common interview point.
  

   Advantages of CTEs
  
     Readability: Breaks complex queries into clear, logical, named blocks.
     Reusability: The same CTE can be referenced multiple times in one query.
     Recursion: Supports recursive CTEs for hierarchical data (org charts, trees).
     Maintainability: Easier to debug and modify than deeply nested subqueries.
  

   CTE vs Derived Table
  
    
      
        
          Feature
          CTE (WITH)
          Derived Table
        
      
      
        
          Defined
          Before the main query
          Inline in FROM
        
        
          Readability
          High
          Lower when nested
        
        
          Reference multiple times?
          Yes
          No (must repeat)
        
        
          Recursion support
          Yes
          No
        
        
          MySQL version
          8.0+
          All versions
        
      
    
  

   Quick Example — Basic CTE
-- Define a CTE, then use it in the main query
WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
)
SELECT d.dept_name, a.avg_salary
FROM dept_avg a
JOIN departments d ON a.dept_id = d.dept_id
WHERE a.avg_salary > 50000;

-- Multiple CTEs in one query
WITH
high_earners AS (
    SELECT * FROM employees WHERE salary > 60000
),
kolkata_depts AS (
    SELECT dept_id FROM departments WHERE location = 'Kolkata'
)
SELECT h.name
FROM high_earners h
JOIN kolkata_depts k ON h.dept_id = k.dept_id;

   Recursive CTE Example
-- Build an employee hierarchy (manager -> subordinates)
WITH RECURSIVE emp_hierarchy AS (
    -- Anchor: top-level manager
    SELECT emp_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive part: find each employee's reports
    SELECT e.emp_id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN emp_hierarchy h ON e.manager_id = h.emp_id
)
SELECT * FROM emp_hierarchy ORDER BY level;

  
     Interviewer tip: The one-liner they want — "A CTE is a named temporary result set defined with WITH that improves readability, can be referenced multiple times, and supports recursion for hierarchical data. It requires MySQL 8.0+."

Feature	CTE (WITH)	Derived Table
Defined	Before the main query	Inline in FROM
Readability	High	Lower when nested
Reference multiple times?	Yes	No (must repeat)
Recursion support	Yes	No
MySQL version	8.0+	All versions

What is a Common Table Expression (CTE) and what are its advantages?

Answer with Explanation

What is a Common Table Expression (CTE) and what are its advantages?

Advantages of CTEs

CTE vs Derived Table

Quick Example — Basic CTE

Recursive CTE Example