SQLMarrow / Course Academy Experience

SQL & Databases Masterclass Course

Welcome to the ultimate postgresql tutorial for beginners 2026. Work through interactive sql practice problems with solutions, master our comprehensive sql window functions tutorial modules, and prepare for tough sql interview questions at Google and Amazon entirely in your browser. Claim your course completion XP and build production-ready database mastery today!

COURSE PROGRESS
0%
0 of 40 Modules Solved
COURSE SYLLABUS
40 Lessons
LESSON WORKSPACEIntermediate

SQL Lesson 19: Common Table Expressions (CTEs)

Introduction & Core Concept

When writing complex queries, you often need to refer to temporary result sets multiple times, or break a giant, multi-join query into smaller, logical blocks. While subqueries are functional, nesting them deeply makes your code unreadable and hard to maintain.

To solve this, SQL provides Common Table Expressions (CTEs). A CTE acts like a temporary, named result set that you define at the very beginning of your query using the WITH clause. You can then reference it just like a regular table within the main query.

WITH TemporaryName AS (
  SELECT column1, column2
  FROM table_name
  WHERE condition
)
SELECT * FROM TemporaryName;
Why & Where We Use It
  • Why We Use It: CTEs dramatically improve query readability, facilitate code reusability (you can reference a CTE multiple times in the same query), and offer a cleaner alternative to nested subqueries.
  • Where We Use It: Writing complex reports, preparing data models, performing multi-stage aggregations, and structuring recursive queries.
    • Real-World Example

    Suppose you run ShopMart. The business wants to see a list of VIP customers who spent more than the average order value. Instead of nesting multiple subqueries inside the WHERE or FROM clause, you can declare a CTE called AverageOrder to compute the average, and then query against it easily.

    Best Practices: What to Do & What NOT to Do
  • What to Do: Give your CTEs highly descriptive names (e.g., CustomerSpending, FilteredCustomers) so that anyone reading your query can understand its purpose instantly.
  • What NOT to Do: Do not misuse CTEs for simple queries. If you only need to filter a single table, writing a CTE adds unnecessary boilerplate.
  • What NOT to Do: Keep in mind that standard CTEs are *temporary* and only exist during the execution of that specific query. They are not stored physically on disk.
    • Syntax & Pro Tips

    You can define multiple CTEs in a single query by separating them with commas:

    WITH CategorySummary AS (
  SELECT category, AVG(price) AS avg_price
  FROM products
  GROUP BY category
),
HighValueCategories AS (
  SELECT category
  FROM CategorySummary
  WHERE avg_price > 100.00
)
SELECT * FROM HighValueCategories;
    Interactive Sandboxed Terminal (Preloaded DB Schema: SHOPMART)
    SQL Query WorkspaceSQLite v3.45 (WASM Mode)
    QUICK INSERT:
    1