#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Science

Common Table Expressions (CTE) in MySQL: WITH Clause Guide

A common table expression (CTE) is a named, temporary result set that you define with WITH at the start of a statement and then use like a table in the rest of that statement. It exists only while that one statement runs. MySQL supports CTEs from version 8.0; MySQL 5.7 and older will give a syntax error.

WITH cte_name AS (
    SELECT ...            -- any query
)
SELECT ... FROM cte_name;  -- use it like a table

Sample data

A small sales table for a team of five sales reps in two regions:

CREATE TABLE sales (
  sale_id   INT PRIMARY KEY,
  rep       VARCHAR(20),
  region    VARCHAR(10),
  sale_date DATE,
  amount    DECIMAL(10,2)
);
INSERT INTO sales VALUES
 (1,'Neha','North','2026-01-05',42000), (2,'Neha','North','2026-02-11',38000),
 (3,'Vikram','North','2026-01-20',25000),(4,'Vikram','North','2026-02-03',21000),
 (5,'Ishaan','South','2026-01-14',51000),(6,'Ishaan','South','2026-02-22',47000),
 (7,'Meera','South','2026-01-09',30000), (8,'Meera','South','2026-02-17',36000),
 (9,'Kabir','South','2026-02-25',18000);

Why use a CTE? The same question two ways

Question: which reps sold more than the average rep in their own region? Without a CTE you need the per-rep totals twice, so the same subquery gets pasted twice, one level deep inside another:

SELECT r.rep, r.region, r.total
FROM (SELECT rep, region, SUM(amount) AS total
      FROM sales GROUP BY rep, region) AS r
JOIN (SELECT region, AVG(total) AS avg_total
      FROM (SELECT rep, region, SUM(amount) AS total
            FROM sales GROUP BY rep, region) AS x
      GROUP BY region) AS a ON a.region = r.region
WHERE r.total > a.avg_total
ORDER BY r.region, r.total DESC;

With CTEs, each step gets a name and is written once. The second CTE reads from the first:

WITH rep_totals AS (
    SELECT rep, region, SUM(amount) AS total
    FROM sales
    GROUP BY rep, region
),
region_avg AS (
    SELECT region, AVG(total) AS avg_total
    FROM rep_totals
    GROUP BY region
)
SELECT r.rep, r.region, r.total, ROUND(a.avg_total, 2) AS region_avg
FROM rep_totals r
JOIN region_avg a ON a.region = r.region
WHERE r.total > a.avg_total
ORDER BY r.region, r.total DESC;
repregiontotalregion_avg
NehaNorth80000.0063000.00
IshaanSouth98000.0060666.67
MeeraSouth66000.0060666.67

Both queries return the same three reps. The CTE version reads top to bottom like the steps you would explain to a colleague: first total each rep, then average per region, then compare. Note the syntax: one WITH, CTEs separated by commas, no comma before the final SELECT.

Referencing one CTE twice: month-over-month growth

A derived table (subquery in FROM) can be used only where it is written. A CTE can be joined to itself. Here the monthly totals are computed once and joined to the previous month:

WITH monthly AS (
    SELECT CAST(DATE_FORMAT(sale_date, '%Y-%m-01') AS DATE) AS month_start,
           SUM(amount) AS revenue
    FROM sales
    GROUP BY month_start
)
SELECT cur.month_start, cur.revenue, prev.revenue AS prev_revenue,
       ROUND(100 * (cur.revenue - prev.revenue) / prev.revenue, 1) AS growth_pct
FROM monthly AS cur
LEFT JOIN monthly AS prev
       ON prev.month_start = cur.month_start - INTERVAL 1 MONTH
ORDER BY cur.month_start;
month_startrevenueprev_revenuegrowth_pct
2026-01-01148000.00NULLNULL
2026-02-01160000.00148000.008.1

(The same result can come from the LAG() window function, covered in the window functions tutorial. CTEs and window functions are often used together.)

CTEs in UPDATE and DELETE

In MySQL 8, WITH can also start an UPDATE or DELETE. This gives every rep who sold ₹60,000 or more a 2% bonus:

CREATE TABLE rep_bonus (rep VARCHAR(20) PRIMARY KEY, bonus DECIMAL(10,2) DEFAULT 0);
INSERT INTO rep_bonus (rep) SELECT DISTINCT rep FROM sales;

WITH totals AS (
    SELECT rep, SUM(amount) AS total FROM sales GROUP BY rep
)
UPDATE rep_bonus b
JOIN totals t ON t.rep = b.rep
SET b.bonus = t.total * 0.02
WHERE t.total >= 60000;

SELECT * FROM rep_bonus ORDER BY bonus DESC, rep;
repbonus
Ishaan1960.00
Neha1600.00
Meera1320.00
Kabir0.00
Vikram0.00

For INSERT ... SELECT, put the WITH after the INSERT INTO ... part: INSERT INTO t (cols) WITH c AS (...) SELECT ... FROM c;

CTE, derived table, view or temporary table?

OptionLives forCan be referenced twiceGood for
CTE (WITH)One statementYesBreaking one complex query into readable steps
Derived tableOne statementNo (repeat it)A single small subquery in FROM
ViewUntil droppedYes, by any queryLogic many queries or users share
Temporary tableUntil the session endsYes, across statementsLarge intermediate results you query many times, or need to index

Common mistakes

If CTEs still feel new, practise with plain subqueries first and then rewrite them as CTEs; you will see quickly where the named steps help.

Launch Your Data Science Career

Learn MySQL, Python, Machine Learning and AI with Uncodemy's Data Science Training in Noida — hands-on projects, industry mentors and Dedicated Placement Support.

Enroll in Data Science Course →