🔍 Subqueries – Queries Within Queries

(because sometimes you need to ask SQL to do something before it can do the main job)

🔧 Quick Syntax

SELECT columns
FROM table
WHERE column IN (SELECT column FROM another_table WHERE condition);

This tells SQL:
“First, run the inner query to find the data, then use that data in the outer query.”

👀 Imagine You Have These Two Tables:

🧑 employees table:

emp_id	name	salary
1	Olivia	60000
2	Mason	75000
3	Zoe	50000
4	Ethan	85000

🛠️ departments table:

dept_id	dept_name	manager_id
1	Marketing	1
2	Sales	2
3	HR	3
4	IT	4

🧑‍💻 Subquery #1: Find Employees in Departments with High-Earning Managers

Scenario: Find all employees who work in departments where the manager earns more than $70,000.

Step 1: Use a Single Subquery to Find Managers Earning More Than $70,000

SELECT emp_id
FROM employees
WHERE salary > 70000;

Result:

emp_id
2
4

These are the managers earning more than $70,000: Mason (emp_id = 2) and Ethan (emp_id = 4).

Step 2: Use that Subquery Result to Find Employees in These Departments

SELECT name
FROM employees
WHERE emp_id IN (
  SELECT manager_id
  FROM departments
  WHERE manager_id IN (SELECT emp_id FROM employees WHERE salary > 70000)
);

✅ Result:

name
Mason
Ethan

Explanation:
– The inner subquery finds managers with a salary greater than $70,000 — Mason and Ethan.
– The outer query then returns the employees who work in the departments managed by these high-earning managers.

🧃 Recap of Subquery #1

• One subquery was used to find managers earning over $70,000.
• The outer query then used those managers to return employees working in their departments.