Common Table Expressions

🧱 Common Table Expressions: Building Blocks for Smart Queries

The Story: Building with LEGO Blocks

Imagine you’re building something amazing with LEGO blocks. Instead of searching through a giant messy pile every time you need a piece, what if you could organize your favorite pieces into neat little boxes first?

That’s exactly what Common Table Expressions (CTEs) do for your SQL queries!

• Regular SQL = Digging through a messy pile every time
• SQL with CTEs = Organized boxes, ready to use

Let’s learn how to become a master builder! 🏗️

🎯 CTE Basics and Benefits

What is a CTE?

A CTE is like giving a nickname to a mini-query. You create it once, then use that nickname whenever you need it.

Think of it like this:

Your teacher asks you to solve: “What is 5 + 3 + 2?”

Instead of doing everything at once, you write:

• Step 1: Let’s call (5 + 3) = “first_sum” → that’s 8
• Step 2: Now add: first_sum + 2 = 10

CTEs work the same way! You break big problems into small, named steps.

Simple Example

WITH smart_students AS (
  SELECT name, score
  FROM students
  WHERE score > 90
)
SELECT * FROM smart_students;

What happened:

1. We created a box called smart_students
2. We filled it with high-scoring students
3. We looked inside the box

Why CTEs are Amazing

graph TD
    A["Big Complex Query"] --> B["Break into CTEs"]
    B --> C["CTE 1: Simple Step"]
    B --> D["CTE 2: Simple Step"]
    B --> E["CTE 3: Simple Step"]
    C --> F["Final Result"]
    D --> F
    E --> F

📝 WITH Clause Syntax

The Magic Words

Every CTE starts with the word WITH. It’s like saying “Hey database, let me define something first!”

The Recipe

WITH your_name AS (
  -- your mini-query here
)
SELECT * FROM your_name;

Breaking It Down

Real Example: Finding Top Sellers

The Story: You run an ice cream shop. You want to find your best-selling flavors.

WITH top_flavors AS (
  SELECT flavor, SUM(sales) AS total
  FROM ice_cream_sales
  GROUP BY flavor
  ORDER BY total DESC
  LIMIT 5
)
SELECT
  flavor,
  total,
  '⭐' AS badge
FROM top_flavors;

Output might look like:

🔗 Multiple CTEs

More Boxes, More Power!

What if you need TWO organized boxes? Or THREE? No problem!

Just separate them with commas.

The Pattern

WITH
  box_one AS (...),
  box_two AS (...),
  box_three AS (...)
SELECT * FROM box_three;

Important: Only ONE WITH at the start, then commas between each CTE!

Real Example: Student Report Card

The Story: A school wants to find students who are good at BOTH math AND reading.

WITH
  math_stars AS (
    SELECT student_name
    FROM grades
    WHERE subject = 'Math'
    AND score >= 85
  ),
  reading_stars AS (
    SELECT student_name
    FROM grades
    WHERE subject = 'Reading'
    AND score >= 85
  )
SELECT m.student_name AS super_student
FROM math_stars m
JOIN reading_stars r
  ON m.student_name = r.student_name;

What we did:

1. Box 1: Found all math stars
2. Box 2: Found all reading stars
3. Final: Found students in BOTH boxes

graph TD
    A["All Students"] --> B["math_stars CTE"]
    A --> C["reading_stars CTE"]
    B --> D["JOIN Together"]
    C --> D
    D --> E["Super Students!"]

CTEs Can Use Other CTEs!

This is where it gets really cool. Later boxes can look inside earlier boxes!

WITH
  all_orders AS (
    SELECT * FROM orders
    WHERE year = 2024
  ),
  big_orders AS (
    SELECT * FROM all_orders
    WHERE total > 100
  )
SELECT * FROM big_orders;

big_orders uses all_orders — like building a tower of LEGO!

🔄 Recursive CTEs

The Magic Trick: A Query That Calls Itself!

Imagine a mirror facing another mirror — you see infinite reflections! Recursive CTEs work similarly. They keep running until there’s nothing left to find.

When Do You Need This?

• Family trees: Finding all ancestors or descendants
• Folder structures: All files inside folders inside folders
• Company charts: Boss → Manager → Employee chains
• Number sequences: Counting 1, 2, 3, 4…

The Special Recipe

WITH RECURSIVE cte_name AS (
  -- Starting point (anchor)
  SELECT ...

  UNION ALL

  -- The loop (recursive part)
  SELECT ...
  FROM cte_name  -- calls itself!
  WHERE ...      -- stop condition
)
SELECT * FROM cte_name;

Example 1: Counting to 5

The Story: Let’s count from 1 to 5, like a rocket countdown!

WITH RECURSIVE counting AS (
  -- Start at 1
  SELECT 1 AS number

  UNION ALL

  -- Add 1 each time
  SELECT number + 1
  FROM counting
  WHERE number < 5
)
SELECT number FROM counting;

Output:

How it works:

graph TD
    A["Start: number = 1"] --> B{Is 1 < 5?}
    B -->|Yes| C["Add: 1+1 = 2"]
    C --> D{Is 2 < 5?}
    D -->|Yes| E["Add: 2+1 = 3"]
    E --> F["...continues..."]
    F --> G{Is 5 < 5?}
    G -->|No| H["STOP!"]

Example 2: Finding All Managers Above You

The Story: Emma wants to know everyone above her at work.

Table: employees

WITH RECURSIVE bosses AS (
  -- Start with Emma
  SELECT id, name, manager_id, 0 AS level
  FROM employees
  WHERE name = 'Emma'

  UNION ALL

  -- Find each person's manager
  SELECT e.id, e.name, e.manager_id,
         b.level + 1
  FROM employees e
  JOIN bosses b ON e.id = b.manager_id
)
SELECT name, level FROM bosses;

Output:

Safety First! ⚠️

Recursive CTEs can run forever if you forget the stop condition!

Always include:

• A WHERE clause that eventually becomes false
• Or use LIMIT as a safety net

-- Safe version with LIMIT
WITH RECURSIVE safe_cte AS (...)
SELECT * FROM safe_cte
LIMIT 1000;  -- Never more than 1000 rows

🎨 Putting It All Together

The Complete Picture

graph TD
    A["CTE Basics"] --> B["Name your query"]
    B --> C["WITH Syntax"]
    C --> D["Multiple CTEs"]
    D --> E["Chain them together"]
    E --> F["Recursive CTEs"]
    F --> G["Query calls itself"]
    G --> H["Master Level! 🏆"]

Quick Reference

Your New Superpower

Before CTEs, your SQL looked like:

SELECT * FROM (
  SELECT * FROM (
    SELECT * FROM (...)
  ) WHERE (...)
) WHERE (...);

With CTEs, it becomes:

WITH
  step1 AS (...),
  step2 AS (...),
  step3 AS (...)
SELECT * FROM step3;

Clean. Clear. Confident. 💪

🚀 You Did It!

You’ve learned:

• ✅ CTE Basics — Naming your queries like LEGO boxes
• ✅ WITH Syntax — The magic recipe
• ✅ Multiple CTEs — Chaining boxes together
• ✅ Recursive CTEs — Queries that explore family trees

Now go build amazing things with your new SQL superpowers! 🌟