Common Table Expressions (CTEs) – WITH Statements

Common Table Expressions (CTEs) – WITH Statements
(because sometimes, SQL needs a short-term memory to make things cleaner)

🔧 Quick Syntax

WITH cte_name AS (
  SELECT ...
)
SELECT ...
FROM cte_name;

This tells SQL:
“Hey, first run this temporary named query (the CTE), and then use it just like a table in the main query.”

What’s a CTE, Really?

A CTE (Common Table Expression) is like giving a nickname to a query result — a temporary view that only lives during that one SQL run.
It’s not a real table.
It’s not saved anywhere.
But it’s SUPER handy when your query starts to look like spaghetti 🍝

Think of it like this:
“Let me prep a little side query first, name it, and then use it like a clean building block.”

Imagine This Table

sales table:

sale_id	rep_name	region	amount
1	Alice	East	1200
2	Bob	East	900
3	Carol	West	1500
4	Dave	West	800
5	Alice	East	700
6	Carol	West	500

Scenario:

You want to find all sales reps who made more than $1000 in total.
You could do this with a subquery, but CTEs make it much easier to read and maintain.

🔧 SQL with CTE

WITH rep_totals AS (
  SELECT rep_name, SUM(amount) AS total_sales
  FROM sales
  GROUP BY rep_name
)
SELECT rep_name, total_sales
FROM rep_totals
WHERE total_sales > 1000;

What’s Happening:

• The WITH block creates a CTE called rep_totals
• Inside that CTE, we calculate the total sales per rep
• The outer query filters for reps with more than $1000 in total sales

Output:

rep_name	total_sales
Alice	1900
Carol	2000

Bonus: Multiple CTEs

WITH regional_totals AS (
  SELECT region, SUM(amount) AS total_region_sales
  FROM sales
  GROUP BY region
),
high_performers AS (
  SELECT rep_name, SUM(amount) AS total_sales
  FROM sales
  GROUP BY rep_name
  HAVING SUM(amount) > 1000
)
SELECT *
FROM high_performers;

You can chain multiple CTEs — just keep them comma-separated, and make sure they’re ordered correctly.

Recap

• A CTE is a temporary named result using WITH
• It helps break complex queries into clean, readable steps
• It disappears after the query finishes — no permanent storage
• Perfect for simplifying subqueries and keeping your logic organized

What Beginners Often Get Confused About

Let’s clear these up right now:

Question	Answer
Is the CTE a real table?	Nope, it’s just temporary. It disappears after the query runs.
Can I use multiple CTEs?	Yes! Just stack them with commas, like a to-do list.
Does it make things faster?	Not always. It’s more about making your query easier to read and write.
Is it the same as a subquery?	Kinda! But CTEs are usually cleaner, reusable, and better for complex logic.