🔄 SQL Correlated Subquery – A Complete Guide with Examples

When working with relational databases, you may come across complex scenarios where a simple subquery isn’t enough. That’s where correlated subqueries come in handy.

In this tutorial, you’ll learn:

• What a correlated subquery is
• How it differs from a regular subquery
• Real-world use cases
• Best practices and performance tips

---

📘 What Is a Correlated Subquery in SQL?

A correlated subquery is a subquery that references columns from the outer query. Unlike a simple (non-correlated) subquery, which runs independently, a correlated subquery is executed once for every row selected by the outer query.

📌 Key Characteristics:

• Depends on values from the outer query
• Executes repeatedly—once per row
• Often used for row-wise comparisons or filtering

---

🧠 Correlated Subquery Syntax

SELECT column1, column2
FROM table1 AS alias1
WHERE column1 operator (
    SELECT column
    FROM table2 AS alias2
    WHERE alias2.column = alias1.column
);

💡 Note: The subquery references a column from the outer query, making it correlated.

---

🔍 Example: Correlated Subquery for Filtering

Let’s find all employees who earn more than the average salary of their own department:

SELECT emp_name, salary, dept_id
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE dept_id = e.dept_id
);

🧾 Explanation:

• The inner subquery calculates average salary per department.
• It refers to e.dept_id, making it dependent on each row of the outer query.

---

🔁 Correlated vs Non-Correlated Subquery

Feature	Correlated Subquery	Non-Correlated Subquery
References outer query	✅ Yes	❌ No
Execution	Once per outer row	Once for entire query
Performance	Slower for large datasets	Faster and more efficient
Use Case	Row-by-row comparison/filtering	Aggregated or static filtering

---

📌 More Real-Life Correlated Subquery Examples

Example 1: Retrieve Products That Are Most Expensive in Their Category

SELECT product_name, category_id, price
FROM products p
WHERE price = (
    SELECT MAX(price)
    FROM products
    WHERE category_id = p.category_id
);

Example 2: Find Employees Who Joined Before Others in Their Department

SELECT emp_name, dept_id, join_date
FROM employees e
WHERE join_date = (
    SELECT MIN(join_date)
    FROM employees
    WHERE dept_id = e.dept_id
);

---

🧩 Correlated Subquery in EXISTS

You can also use correlated subqueries with EXISTS for efficient filtering.

SELECT dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.dept_id = d.dept_id
);

🔍 Use Case: Check if a department has any employees.

---

⚠️ Performance Tips for Correlated Subqueries

• Avoid using them on large datasets without proper indexing
• Use JOINs or WITH (CTEs) if performance is critical
• Analyze execution plans using EXPLAIN in MySQL/PostgreSQL or SQL Server tools

---

✅ When to Use Correlated Subqueries

Use correlated subqueries when:

• You need row-level logic
• The condition must adapt based on current outer row
• JOINs or simple subqueries can’t capture the logic easily

---

📝 Summary

A correlated subquery is a powerful SQL technique used when you need to compare each row to a dynamically calculated value from a related dataset. While it’s more performance-intensive than a standard subquery, it’s invaluable for scenarios that require row-by-row logic.

By understanding how and when to use correlated subqueries, you’ll be better equipped to solve advanced SQL problems in data analysis, reporting, and application development.