Difference Between Regular Subquery and Correlated Subquery

Feature	Regular (Non-Correlated) Subquery	Correlated Subquery
Depends on outer query?	❌ No — runs independently.	✅ Yes — refers to columns from the outer query.
Execution	Executed once.	Executed once for each row of the outer query.
Performance	Usually faster — one execution.	Can be slower — multiple executions.
Usage	Common in WHERE, HAVING, SELECT, FROM.	Often in WHERE or SELECT clauses.

---

1. Regular (Non-Correlated) Subquery

Runs once — result is used for the entire outer query.

Example

Find all products with a price higher than the average price:

SELECT name, price
FROM Products
WHERE price > (SELECT AVG(price) FROM Products);

• (SELECT AVG(price) FROM Products) is calculated once.
• The outer query uses that value to compare for all rows.

✅ Independent — no reference to the outer query.

2. Correlated Subquery

The subquery depends on the current row of the outer query — it repeats for each row.

Example

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

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

• For each employee e1, the subquery:
  • Calculates the average salary of that employee’s department.
• e2.department = e1.department — references the outer query.

✅ Dependent — the subquery uses values from the current row of the outer query.

Key Differences Visually

Regular Subquery (Run Once)
Outer query (uses one subquery result)
└── Subquery (runs once, gives a constant)

Correlated Subquery (Run per Row)
Outer query (for each row)
└── Subquery (uses values from the outer row)

---

Why Correlated Subqueries Are Slower

• If you have 10,000 rows, a regular subquery runs once.
• A correlated subquery might run 10,000 times — once for each row!
• More work → more time → slower on big data.

---

Real-Life Analogy

Regular Subquery	Correlated Subquery
Ask: “What’s the average height of all people?”	Ask: “What’s the average height of people in your city?” (for every person)

---

In Short

Regular Subquery	Correlated Subquery
Runs once.	Runs once for each row.
Independent of outer query.	Depends on outer query’s current row.