Skip to content

SQL WITH Clause (CTE) 🏗️

Prerequisites: SELECT Statement, Subqueries

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

graph TD
    A[Step 1: Get Raw Sales] --> B[CTE: SalesData]
    C[Step 2: Get Raw Users] --> D[CTE: UserData]
    B --> E[Final JOIN: Combine Results]
    D --> E

💡 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