Views & Common Table Expressions (CTEs)

« ACID Transactions & Row-Level Locking« Previous Stored Procedures & Automated Triggers »Next »

Simplify complex queries with reusable Virtual Views and modern Common Table Expressions (WITH clauses).

Common Table Expressions (CTEs)

Introduced in MySQL 8.0, CTEs (WITH clause) create temporary named result sets that make nested queries readable and modular.

Using a CTE to Calculate Customer Tiers

Try It Yourself »

WITH CustomerSpend AS (
    SELECT UserID, SUM(OrderTotal) AS Total
    FROM Orders
    GROUP BY UserID
)
SELECT UserID, Total,
       CASE 
           WHEN Total > 1000 THEN 'VIP'
           WHEN Total > 250 THEN 'Regular'
           ELSE 'New'
       END AS CustomerTier
FROM CustomerSpend;

Google AdSense Slot (inContentBanner)

Set your Publisher ID in src/config/ads.ts to display live ads.

Test Yourself With Exercise

+15 XP

Which SQL clause introduces a Common Table Expression (CTE) in MySQL 8.0+?

« Previous: ACID Transactions & Row-Level Locking Next: Stored Procedures & Automated Triggers »