SQL Correlated Subqueries (Row-by-Row Power Tools)

🔹 Why Correlated Subqueries Matter

Correlated subqueries are inner queries that reference columns from the outer query. They execute once per outer row, making them perfect for row-aware comparisons like “employees earning above their department average.”

⚙️ 1️⃣ How Correlated Subqueries Work

SELECT column_list
FROM table1 outer
WHERE outer.column > (
    SELECT AVG(inner.column)
    FROM table2 inner
    WHERE inner.key = outer.key
);

Unlike regular subqueries, correlated subqueries cannot run independently because they depend on the current outer row.

⚙️ 2️⃣ Sample Tables

Employees	Departments
EmpID, EmpName, DeptID, Salary	DeptID, DeptName
1 – Suresh – 10 – 60000	10 – HR
2 – Priya – 10 – 80000	20 – IT
3 – Raj – 20 – 55000	30 – Finance
4 – Anil – 20 – 70000
5 – Meena – 30 – 90000

⚙️ 3️⃣ Example – Above Department Average

SELECT e.EmpName, e.Salary, e.DeptID
FROM Employees e
WHERE e.Salary > (
    SELECT AVG(e2.Salary)
    FROM Employees e2
    WHERE e2.DeptID = e.DeptID
);

Returns Priya, Anil, and Meena.

⚙️ 4️⃣ Example – Highest Salary per Department

SELECT e.EmpName, e.Salary, e.DeptID
FROM Employees e
WHERE e.Salary = (
    SELECT MAX(e2.Salary)
    FROM Employees e2
    WHERE e2.DeptID = e.DeptID
);

⚙️ 5️⃣ Example – Departments with High Earners

SELECT DISTINCT d.DeptName
FROM Departments d
WHERE EXISTS (
    SELECT 1
    FROM Employees e
    WHERE e.DeptID = d.DeptID
      AND e.Salary > 75000
);

⚙️ 6️⃣ Example – Row-by-Row Comparisons

SELECT e1.EmpName, e1.Salary
FROM Employees e1
WHERE e1.Salary > ALL (
    SELECT e2.Salary
    FROM Employees e2
    WHERE e2.DeptID = e1.DeptID
      AND e2.EmpName <> e1.EmpName
);

⚙️ 7️⃣ Example – Updates with Correlation

UPDATE Employees e
SET e.Salary = e.Salary * 1.10
WHERE e.Salary < (
    SELECT AVG(e2.Salary)
    FROM Employees e2
    WHERE e2.DeptID = e.DeptID
);

⚙️ 8️⃣ Performance Tips

Tip	Description
Avoid on huge datasets	Correlated subqueries execute per row, causing repeated scans.
Prefer joins/CTEs	Many patterns can be rewritten with `JOIN` or `WITH`.
Index foreign keys	Boosts lookup speed for the inner query.
Use EXISTS over IN	`EXISTS` short-circuits as soon as a match is found.
Leverage analytics	`AVG() OVER (PARTITION BY DeptID)` often replaces correlated logic.

⚙️ 9️⃣ Rewrite with Analytical Function

SELECT EmpName, Salary, DeptID
FROM (
    SELECT e.*, AVG(Salary) OVER (PARTITION BY DeptID) AS DeptAvg
    FROM Employees e
) t
WHERE Salary > DeptAvg;

🧩 Real-World Use Cases

Employee Analytics

Identify staff earning above department averages or below thresholds.

Sales Outliers

Spot stores outperforming regional averages.

Product Pricing

Detect items priced above category norms.

API Validation

Cross-check payload data based on related table conditions.

⚖️ Best Practices

Use correlated subqueries for row-aware checks that are hard to express otherwise.
Index correlation columns (like DeptID) to reduce re-execution cost.
Consider rewriting with window functions or joins for heavy workloads.
Inspect execution plans to understand cost drivers.

💡 Interview Tip

“A correlated subquery runs once per outer row. Explain when you would still choose it and how to mitigate performance (indexes, EXISTS, analytics).”

← Previous Next →

← Back to Articles | 🏠 Back to Homepage