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 🪆

---

💻 Implementations (Dialect Comparison)

Oracle SQL

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

MySQL

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

PostgreSQL

-- 🚀 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) →