SQL Subqueries and Nested Queries Explained
💡 Concept Name
SQL Subqueries (or nested queries) are queries embedded within another SQL statement, allowing complex data retrieval and filtering.
📘 Quick Intro
Subqueries are enclosed in parentheses and can be used in SELECT, FROM, or WHERE clauses. They return a value or set of values used by the outer query. Types include simple subqueries and correlated subqueries, which reference the outer query.
🧠 Analogy / Short Story
Think of a subquery like looking up a phone number in a directory before calling. You first find the contact (inner query) and then place the call (outer query). In SQL, the inner query gives you data that the outer query uses to filter, join, or compute. It’s like solving one problem as a step to solving a bigger one.
🔧 Technical Explanation
- 🧩 Subqueries can return scalar (single value), row, or a table result.
- 🔄 Correlated subqueries refer to columns from the outer query and are evaluated row by row.
- 📍 Subqueries can be used in SELECT, FROM, or WHERE clauses.
- 🎯 Useful for filtering, comparison, existence checks, and derived tables.
- ⚠️ May affect performance—consider using joins for efficiency in some cases.
🎯 Purpose & Use Case
- ✅ Filter rows based on aggregate or condition not available in the outer table.
- ✅ Use in reporting or when conditional logic depends on related data.
- ✅ Check for existence of values in another table.
- ✅ Replace joins when needing specific flexibility in data manipulation.
💻 Real Code Example
-- Subquery in WHERE clause
SELECT name
FROM Employees
WHERE department_id = (
SELECT id
FROM Departments
WHERE name = 'Sales'
);
-- Correlated subquery
SELECT e.name
FROM Employees e
WHERE salary > (
SELECT AVG(salary)
FROM Employees
WHERE department_id = e.department_id
);
❓ Interview Q&A
Q1: What is a subquery?
A: A query nested inside another query to provide intermediate results used by the outer query.
Q2: Where can subqueries be used?
A: In SELECT, FROM, or WHERE clauses depending on context.
Q3: What is a correlated subquery?
A: A subquery that references columns from the outer query and is re-evaluated for each row.
Q4: How does SQL process nested queries?
A: The inner query is evaluated first, and its result is passed to the outer query.
Q5: Can a subquery return multiple rows?
A: Yes, and in such cases operators like IN, ANY, or EXISTS should be used.
Q6: What's the difference between JOIN and subquery?
A: Joins combine data directly, while subqueries use intermediate steps—joins are often faster.
Q7: What happens if a subquery returns more than one row where one is expected?
A: SQL will throw an error unless handled with IN or EXISTS.
Q8: Can you use ORDER BY in a subquery?
A: Not unless the subquery is in a FROM clause or wrapped with TOP or LIMIT.
Q9: What is the performance impact of subqueries?
A: Subqueries, especially correlated ones, may be slower than joins in large datasets.
Q10: Are subqueries supported in all SQL dialects?
A: Yes, but syntax and features may vary slightly across databases.
📝 MCQs
Q1. Where is a subquery typically placed?
- In WHERE only
- After GROUP BY
- Inside parentheses
- At the end
Q2. What does a subquery return?
- Nothing
- A separate table
- Value(s) used by outer query
- Always one row
Q3. What is a correlated subquery?
- Independent query
- Join query
- Stored procedure
- Depends on outer query
Q4. Which clause can contain a subquery?
- SELECT
- WHERE
- FROM
- All of the above
Q5. What happens if subquery returns multiple rows?
- Error
- Fails silently
- Use IN/EXISTS
- Only takes first
Q6. What does EXISTS check?
- Sum of values
- Condition match
- If subquery returns any row
- Schema match
Q7. Can you nest multiple levels of subqueries?
- No
- Yes
- Only 2 levels
- Only in SELECT
Q8. Are subqueries faster than joins?
- Always
- Never
- Only in MySQL
- Not always
Q9. What keyword is required for multi-row subqueries?
- LIKE
- IN
- AS
- ORDER
Q10. Which is evaluated first?
- Outer query
- Subquery
- JOIN
- Group By
💡 Bonus Insight
Use subqueries to break complex logic into smaller parts, but analyze execution plans to ensure performance. Replace correlated subqueries with joins when dealing with large datasets. Consider common table expressions (CTEs) for readability.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!