MySQL WITH Clause & CTEs - A Complete Guide with Examples

Stop Nesting Subqueries — Use CTEs Instead 🧠

Ever written a SQL query so deeply nested you couldn't
remember what the outer SELECT was even doing?

Yeah. We've all been there.

Common Table Expressions (CTEs) — introduced in MySQL 8.0
via the WITH keyword — are the clean, readable alternative
you've been looking for.

What you'll learn in this guide

• 🔹 What a CTE actually is (no fluff)
• 🔹 Basic WITH clause syntax
• 🔹 CTEs vs Subqueries — side by side
• 🔹 Chaining multiple CTEs in one query
• 🔹 WITH RECURSIVE for hierarchies & org charts
• 🔹 Real-world examples: running totals, deduplication, DELETE
• 🔹 Pitfalls to avoid (infinite loops, indexing, MySQL version)

The core idea in 10 seconds

WITH big_orders AS (
    SELECT order_id, customer_id, total_amount
    FROM   orders
    WHERE  total_amount > 500
)
SELECT c.customer_name, b.order_id, b.total_amount
FROM   big_orders b
JOIN   customers c ON c.customer_id = b.customer_id;

That's it. You named a query block big_orders and used it
like a table. No nested mess. No repeated logic.

Ready to level up your SQL?

👉 Read the full guide

─────────────────────────────
💬 Do you already use CTEs in your projects?
What's the most complex CTE you've written?
Share it below — let's learn from each other! 👇
─────────────────────────────