Oracle Subqueries (Russian Dolls) 🪆

Mentor's Note: A subquery is just a query inside another query. Think of it like a "Helper question". You ask one question to get an answer, then use that answer to solve the main problem. 💡

---

🌟 The Scenario: The Average Earner 💰

• The Problem: Who earns more than the average?
• Step 1 (Inner): Find the average salary.
• Step 2 (Outer): Find people whose salary > Step 1. ✅

---

💻 1. Basic Subquery

The inner query runs once first.

SELECT first_name, salary 
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

---

💻 2. Correlated Subquery 🔗

The inner query runs once for every row in the outer query. It's slower but much more powerful.

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

---

💻 3. EXISTS & NOT EXISTS 🚪

Checks if any rows are returned. It doesn't care what is returned, just if it's there.

-- Find departments that have at least one employee
SELECT department_name FROM departments D
WHERE EXISTS (SELECT 1 FROM employees WHERE department_id = D.department_id);

---

💻 4. ANY, SOME, and ALL

Used to compare a single value against a list.

• > ANY: Higher than the minimum in the list.
• > ALL: Higher than the maximum in the list.

-- Salary higher than ALL IT programmers
SELECT first_name FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG');

---

🏗️ Architect's Note: Performance - EXISTS vs IN 🛡️

In old versions of Oracle, EXISTS was much faster than IN. In modern versions, the Oracle Optimizer is smart enough to turn an IN into an EXISTS automatically.

• The Architect's Secret: EXISTS is still slightly better for large datasets because it uses Semi-Join logic—it stops as soon as it finds the first match!

---

📈 Learning Path

• ← Back: Advanced Grouping
• Next: Table Structures (DDL) →