SQL Subqueries (The Russian Doll) 🪆¶

Mentor's Note: Sometimes you need to find an answer before you can ask the main question. "Who earns more than the average?" First, you need to find the average (Inner Query), then compare everyone to it (Outer Query). This is a Subquery. 💡

🌟 The Scenario: The Salary Audit 💰¶

Imagine you are the HR Manager. - Step 1 (Inner Question): "What is the average salary in the company?" -> Result: ₹50,000. - Step 2 (Outer Question): "Now, give me the names of everyone who earns more than that ₹50,000." - The Subquery: Combines both steps into one single execution. ✅

💻 1. Simple Subquery (In WHERE)¶

This is the most common type. The inner query runs first, and its result is used by the outer query.

-- Scenario: Find students with marks higher than Arjun's marks
SELECT name, marks
FROM students
WHERE marks > (
    SELECT marks FROM students WHERE name = 'Arjun'
);

💻 2. Correlated Subquery (The Dependent) 🔗¶

Unlike simple subqueries, a Correlated Subquery depends on the outer query for its values. It runs once for every row in the outer query.

-- Scenario: Find employees who earn more than the average of THEIR OWN department
SELECT emp_name, salary, dept_id
FROM employees E
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees 
    WHERE dept_id = E.dept_id -- 🔗 Linked to outer row
);

🎨 Visual Logic: Nested Execution¶

graph TD
    A[Outer Query] -- "Sends ID" --> B[Inner Query]
    B -- "Calculates & Returns Result" --> A
    A -- "Filters Row based on Result" --> C[Final Result Set]

📊 Subqueries vs Joins¶

Which one should you use?

Feature	Subqueries	Joins
Readability	Easier for "Find X who has Y"	Easier for "Combine X and Y"
Performance	Can be slower if Correlated 🐢	Usually faster (highly optimized) ⚡
Logic	Isolated steps	Flat relationships

💡 Pro Tip¶

"Use Subqueries when you need a single value (like an Average or Max) to filter a list. Use Joins when you need to display columns from multiple tables side-by-side."