What is a Subquery and Where Can It Be Used?
💡 Concept Name
Subquery (Nested Query) – A query inside another SQL query used to return intermediate results for filtering or calculation.
📘 Quick Intro
A subquery is a query embedded within another SQL statement like SELECT, INSERT, UPDATE, or DELETE. It allows using results from one query to filter or calculate in the outer query.
🧠 Analogy / Short Story
Think of a subquery like asking a helper for some specific information before you decide your next move. It’s like checking the weather forecast before planning a picnic – you get an answer first, then act based on it.
🔧 Technical Explanation
- Subqueries are enclosed in parentheses and can be placed in SELECT, WHERE, FROM, or HAVING clauses.
- There are two main types: correlated and non-correlated subqueries.
- Non-correlated subqueries run independently, while correlated subqueries depend on outer query values.
- Subqueries simplify complex queries by breaking them into manageable parts.
💻 Real Code Example
-- Non-correlated subquery: find customers who placed orders
SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerID IN (
SELECT CustomerID FROM Orders
);
-- Correlated subquery: find orders with amount greater than average order amount
SELECT OrderID, OrderAmount
FROM Orders O1
WHERE OrderAmount > (
SELECT AVG(OrderAmount) FROM Orders O2 WHERE O1.CustomerID = O2.CustomerID
);
❓ Interview Q&A
Q1: What is a subquery?
A: A query nested inside another query used to retrieve data for the outer query.
Q2: What are the types of subqueries?
A: Correlated and non-correlated subqueries.
Q3: Where can subqueries be used?
A: In SELECT, WHERE, FROM, and HAVING clauses.
Q4: What is a correlated subquery?
A: A subquery that references columns from the outer query.
Q5: Can subqueries improve query readability?
A: Yes, by breaking down complex logic into simpler parts.
Q6: How is a subquery different from a JOIN?
A: Subqueries nest queries inside, while JOINs combine tables horizontally.
Q7: Can subqueries return multiple rows?
A: Yes, depending on the query design.
Q8: Are subqueries always efficient?
A: Not always; correlated subqueries can be slower than JOINs.
Q9: Can you nest multiple subqueries?
A: Yes, subqueries can be nested inside other subqueries.
Q10: What is the role of parentheses in subqueries?
A: They define the scope of the subquery.
📝 MCQs
Q1. What is a subquery?
- A query inside another query
- A type of index
- A table join
- A stored procedure
Q2. Where can subqueries be used?
- Only SELECT clause
- Only WHERE clause
- SELECT, WHERE, FROM, HAVING clauses
- Only in UPDATE statements
Q3. What is a correlated subquery?
- Independent query
- Subquery referencing outer query columns
- Query joining two tables
- A type of trigger
Q4. Can subqueries return multiple rows?
- No
- Yes
- Sometimes
- Only in UPDATE
Q5. Which clause can subqueries NOT be used in?
- SELECT
- WHERE
- FROM
- GROUP BY
Q6. How does a subquery differ from a JOIN?
- Subqueries are faster
- JOINs nest queries
- Subqueries nest, JOINs combine tables
- They are the same
Q7. What is a non-correlated subquery?
- Depends on outer query
- Runs independently of outer query
- Uses JOIN
- Is a function
Q8. Are subqueries always efficient?
- Yes
- No
- Sometimes
- Depends on indexes
Q9. Can subqueries be nested?
- No
- Yes
- Only two levels
- Depends on DBMS
Q10. What do parentheses do in subqueries?
- Group JOINs
- Define subquery scope
- Ignore NULLs
- No effect
💡 Bonus Insight
Subqueries can simplify complex SQL statements, but sometimes rewriting with JOINs can improve performance. Understanding when to use each is key for efficient database querying.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!