Correlated Subquery vs Regular Subquery in SQL
💡 Concept Name
Correlated Subquery – A subquery that references columns from the outer query, executing once per row of the outer query.
Regular Subquery – A subquery that is independent and executed once before the outer query.
📘 Quick Intro
Regular subqueries run once and provide a result set for the outer query. Correlated subqueries depend on outer query values and run repeatedly for each row processed.
🧠 Analogy / Short Story
Think of a regular subquery as ordering a fixed menu in a restaurant before you arrive — it doesn’t change. A correlated subquery is like asking the waiter about the daily special based on the table you’re sitting at — it depends on your seat and changes per table.
🔧 Technical Explanation
- A regular subquery executes once and its result is used by the outer query.
- A correlated subquery references columns from the outer query and executes once per outer row.
- Correlated subqueries tend to be slower due to repeated execution.
- Regular subqueries are often simpler and used for filtering or scalar values.
- Correlated subqueries are useful for row-wise comparisons or existence checks.
💻 Code Examples
Regular Subquery Example:
SELECT EmployeeName
FROM Employees
WHERE DepartmentID IN (
SELECT DepartmentID
FROM Departments
WHERE Location = 'New York'
);
Correlated Subquery Example:
SELECT e.EmployeeName, e.Salary
FROM Employees e
WHERE e.Salary > (
SELECT AVG(e2.Salary)
FROM Employees e2
WHERE e2.DepartmentID = e.DepartmentID
);
❓ Interview Q&A
Q1: What is a correlated subquery?
A: A subquery that references columns from the outer query and runs once per outer row.
Q2: How does a regular subquery differ from a correlated subquery?
A: A regular subquery runs once independently, while a correlated subquery runs repeatedly for each row.
Q3: Which subquery is generally faster?
A: Regular subqueries are usually faster due to single execution.
Q4: Can correlated subqueries be used for row-wise comparisons?
A: Yes, they are ideal for such operations.
Q5: When would you use a regular subquery?
A: When the subquery result does not depend on the outer query.
Q6: Are correlated subqueries supported by all major RDBMS?
A: Yes, but performance may vary.
Q7: Can a correlated subquery be rewritten as a JOIN?
A: Often yes, for better performance.
Q8: Does a correlated subquery always reference outer query columns?
A: Yes, that is what distinguishes it.
Q9: Is a correlated subquery useful for EXISTS clauses?
A: Yes, often used in EXISTS conditions.
Q10: Can regular subqueries return multiple rows?
A: Yes, depending on the query design.
📝 MCQs
Q1. What is a correlated subquery?
- Runs independently
- Depends on outer query's columns
- Runs once
- Is not a subquery
Q2. How many times does a correlated subquery execute?
- Once
- Once per outer row
- Never
- Depends on DBMS
Q3. Does a regular subquery run once or multiple times?
- Multiple times
- Once
- Depends on outer query
- Never
Q4. Which is generally faster?
- Correlated subquery
- Regular subquery
- Both same
- Neither
Q5. Can a correlated subquery reference outer query columns?
- No
- Yes
- Sometimes
- Only in certain DBMS
Q6. Can correlated subqueries be rewritten as JOINs?
- No
- Often yes
- Never
- Sometimes
Q7. Are correlated subqueries useful for row-wise comparisons?
- No
- Yes
- Maybe
- Depends
Q8. Do correlated subqueries execute once per outer query row?
- No
- Yes
- Sometimes
- Rarely
Q9. Is a regular subquery independent of the outer query?
- No
- Yes
- Sometimes
- Depends
Q10. Can regular subqueries return multiple rows?
- No
- Yes
- Depends
- Only single value
💡 Bonus Insight
While correlated subqueries can be intuitive for row-wise checks, they can be less efficient. Consider rewriting them as JOINs or using window functions for better performance in large datasets.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!