Correlated Subqueries – Referring to the Outer Query

🔁 Correlated Subqueries – Referring to the Outer Query
(because sometimes SQL needs to peek outside while doing the inside job)

Quick Syntax

SELECT columns  
FROM table AS outer  
WHERE column OPERATOR (  
  SELECT column  
  FROM table AS inner  
  WHERE inner.column = outer.column  
);

What this says:
“Hey SQL, for each row in the outer query, run the inner query — and use info from the outer query inside the subquery.”

That’s what makes it correlated – the inner query depends on the outer one.

Imagine These Two Tables:

🧑‍💼 employees table:

emp_id	name	department	salary
1	Ava	Sales	60000
2	Leo	Sales	72000
3	Mia	HR	55000
4	Noah	HR	62000
5	Emma	IT	80000

🧑‍💻 Scenario:

Find employees who earn more than the average salary in their department.

You can’t just run a simple subquery here — because the “average salary” depends on which department the employee is in.
That’s where a correlated subquery saves the day.

🔧 SQL Time

SELECT name, department, salary  
FROM employees AS e1  
WHERE salary > (  
  SELECT AVG(salary)  
  FROM employees AS e2  
  WHERE e2.department = e1.department  
);

Let’s walk through it:

• e1 is the outer query – looping through each employee
• For each row, the inner query (on e2) calculates the average salary of that employee’s department
• If the employee’s salary is greater than that average → include them in the result

✅ Result:

name	department	salary
Leo	Sales	72000
Noah	HR	62000
Emma	IT	80000

Why these folks?

• Leo earns more than the average in Sales (which is 66000)
• Noah earns more than the average in HR (58500)
• Emma is the only person in IT — so she technically earns more than the average (which is just her)

🎯 Why Use a Correlated Subquery?

Because sometimes, the inner query needs context from the outer row.
It’s like asking:
“For THIS person, what’s the average in THEIR group?”

Each row gets its own mini subquery run. Powerful stuff.

Recap

• A correlated subquery uses values from the outer query inside the inner query
• It runs once per row in the outer query
• Super useful when your logic depends on row-specific comparisons (like “more than the department average”)