SQL Subqueries 🚀¶

Mentor's Note: A subquery is essentially a "Question within a Question." It's one of the most powerful tools for solving complex business problems in a single step! 💡

🌟 The Scenario: The Inception Logic 🎞️¶

Imagine you want to find all students who scored higher than the average mark.

The Problem: You don't know the average mark yet! 📦
The Inner Query: First, you ask the database: "What is the average mark?" (Query 1). 🔢
The Outer Query: Then, you use that answer to ask: "Now show me everyone who scored higher than [Answer 1]." (Query 2). 📦
The Result: A perfectly nested solution. ✅

📖 Concept Explanation¶

1. What is a Subquery?¶

A subquery is a SQL query nested inside a larger query. It is also known as an Inner Query or Nested Query.

2. Common Placements¶

In WHERE: Filter data based on dynamic values.
In FROM: Use the result of one query as a "Virtual Table" for another.
In SELECT: Pull in a single specific value from a related table.

🎨 Visual Logic: The Russian Doll 🪆¶

graph TD
    subgraph Outer_Query ["Outer Query: SELECT * FROM Students WHERE marks > (...)"]
        subgraph Inner_Query ["Inner Query: SELECT AVG(marks) FROM Students"]
            A[Logic Step 1: Calculate Average]
        end
        B[Logic Step 2: Compare every student to that Average]
    end

💻 Implementations (Dialect Comparison)¶

Oracle SQLMySQLPostgreSQL

-- 🚀 Action: Find employees earning more than the company average
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 🚀 Action: Same logic as Oracle
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 🚀 Action: Same logic as Oracle
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

📊 Sample Dry Run¶

Goal: Find students in the same city as 'Vishnu'

Step	Query Part	Logic	Result
1	Inner	Find city of 'Vishnu'	`"Surat"`
2	Outer	`SELECT name FROM students WHERE city = "Surat"`	`Ankit, Priya...`

📉 Technical Analysis¶

Execution Order: The database usually runs the Inner Query once first, gets the result, and then hands it over to the Outer Query.
Correlated Subqueries: These are special subqueries that run once for every row in the outer query. They are powerful but can be very slow! ⚠️

🎯 Practice Lab 🧪¶

Task: The Empty Department

Task: Find all departments in the DEPARTMENTS table that have no employees. Hint: Use WHERE dept_id NOT IN (SELECT dept_id FROM employees). 💡

💡 Interview Tip 👔¶

"Interviewers love asking: 'What is the difference between a Join and a Subquery?' Answer: A Join is usually faster for large data, but a Subquery is often easier to read for complex filtering logic!"

💡 Pro Tip: "Inception is possible. You just need to go deeper." - Dom Cobb (Inception)

← Back: Grouping | Next: Data Management (DDL) →