SQL WITH Clause (CTE) 🏗️

Mentor's Note: Think of the WITH clause as a "Bookmark". If you have a huge, messy query, you can bookmark a part of it, give it a name, and then use that name later. It turns "Spaghetti Code" into organized logic. 💡

---

🌟 The Scenario: The Recipe 🥘

Imagine you are making a complex dish.

• Method A (Subqueries): You try to chop onions, boil water, and fry chicken all in one tiny pan at the same time. (Messy! 🤯)
• Method B (WITH Clause):
  1. Prepare the Sauce. (The Sauce CTE) 🥣
  2. Cook the Meat. (The Meat CTE) 🥩
  3. Combine them into the Final Plate. ✅

---

💻 1. The Basic Syntax

WITH cte_name AS (
    SELECT ... -- Your logic here
)
SELECT * FROM cte_name; -- Use it like a table

Example: High Earning Filter

-- Scenario: Find employees earning more than 50k and join them with Dept
WITH HighEarners AS (
    SELECT emp_name, salary, dept_id 
    FROM employees 
    WHERE salary > 50000
)
SELECT H.emp_name, D.dept_name
FROM HighEarners H
JOIN departments D ON H.dept_id = D.id;

---

💻 2. Why use CTEs? (Benefits) 📈

1. Readability: You read the logic from top to bottom.
2. No Redundancy: You can reference the same CTE multiple times in one query.
3. Replacement for Subqueries: Much easier to debug than nested "Select within Select".

---

🎨 Visual Logic: The Modular Build

---

💡 Interview Tip 👔

"Interviewers often ask: 'Is a CTE permanent?' Answer: NO. A CTE only exists while the query is running. Once the query ends, the bookmark is deleted. Use a VIEW if you want it to be permanent!"

---

📈 Learning Path

• ← Back: Aliases (AS)
• Next: GROUP BY & Aggregation →