Ever written a SQL query? Then you’ve seen a subquery. You know, that tiny query inside another query. Pretty simple but here’s the cool part. You can put a query inside a query. Then put another one inside that! Yep, you can nest them lots of times.
Sounds weird at first but once you get it, it’s easy. Really easy. Nesting makes your SQL super strong and smart too.
What Is a Subquery?
It is just a query inside another query. Think of it like a tiny helper. It gets data for the main query.
Suppose you want to find workers who earn more than average. You could use two queries. One finds the average. The other compares it. But with a subquery? You do it all at once!
Example:
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
See the part in brackets? That’s your subquery. It finds the average pay. Then the main query checks who earns more.
Why Subqueries Are Useful
Subqueries save you time. And code too. They make your SQL cleaner. No more separate queries. No more joining tables over and over. Just let one query help another.
You can use them to:
• Filter stuff based on other data
• Compare data from two tables
• Add up totals or find averages
• Find the biggest or smallest values
• Skip joins sometimes
Types of Subqueries
Before we nest, let’s see where they go.
In the WHERE clause: filters rows.
Example:
SELECT * FROM students WHERE score > (SELECT AVG(score) FROM students);
In the FROM clause: makes a temp table.
Example:
SELECT temp.student_name, temp.score
FROM (SELECT student_name, score FROM students WHERE score > 80) AS temp;
In the SELECT clause: shows extra math stuff.
Example:
SELECT name, (SELECT AVG(score) FROM students) AS average_score FROM students;
Now for the fun part. Let’s nest them!
What Is Nesting in Subqueries?
Nesting means putting one subquery inside another. Like layers, you know? The deepest one runs first. Then it sends the results up.
Example:
SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = ‘IT’));
Breakdown:
• First query finds the IT department ID
• Next query finds average pay for IT
• Last query finds people who earn more
Three queries in one. Nice!
How Many Times Can You Nest a Subquery?
No fixed limit really. But it depends on your database. MySQL, SQL Server, PostgreSQL? They can handle lots.
But don’t go crazy. Too deep makes things slow. And hard to read. Two or three levels? Perfect. More than that? Maybe use joins instead.
Example of Multiple Nested Subqueries
Want to find students who scored higher than… wait for it… the average of those who beat the class average? Sounds hard? Watch this.
SELECT name
FROM students
WHERE score > (SELECT AVG(score)
FROM students
WHERE score > (SELECT AVG(score) FROM students));
Here’s what happens:
• Inner query finds class average
• Middle query finds average of top kids
• Outer query finds who beat that
Three layers. One statement. Boom!
Breaking Down the Execution Order
SQL works from inside out. Like this:
- Deepest query runs first. Get a result.
- The next query uses that result.
- The main query uses the result from that.
It’s like dominoes. Each needs the one before it.
Advantages of Nested Subqueries
Nesting is powerful. Here’s why:
• Write complex stuff in one go
• No extra temp tables needed
• Test each part alone
• Compare results from different rules
When to Use Nested Subqueries
Use them when:
• You need to compare data levels
• You filter based on another query
• You want logic that builds on itself
Example: Real-World Case
Say you have three tables:
• customers (id, name)
• orders (id, customer_id, total)
• countries (id, name)
Want to find customers who spent more than the India average?
SELECT name
FROM customers
WHERE id IN (SELECT customer_id
FROM orders
WHERE total > (SELECT AVG(total)
FROM orders
WHERE customer_id IN (SELECT id
FROM customers
WHERE country_id = (SELECT id FROM countries WHERE name = ‘India’))));
Four layers! Let me explain:
• First finds India’s ID
• Next finds all India customers
• Next finds their average spending
• Last finds who spent more
That’s how you solve hard problems. In one shot!
Tips for Writing Nested Subqueries
• Start small. Build the inner query first. Test it alone.
• Move out one step at a time.
• Use clear names to read easier.
• Don’t nest too deep if speed matters.
• Try joins for faster results sometimes.
Performance Considerations
Nesting is cool but not always fast. Each subquery adds work. Big tables? Many times? Things slow down.
How to fix it:
• Use indexes on key columns
• Filter data early
• Replace deep nests with joins or CTEs
• Cache results if you can
Mistakes to Avoid
• Forgetting brackets in nested queries
• Using too many levels when joins work better
• Not testing each part alone
• Ignoring speed with big data
• Not using names when needed
Key Takeaways
• A subquery is a query inside another
• You can nest them lots of times
• Inner queries run first
• Nesting solves hard problems in one query
• Too much nesting slows things down
• Use CTEs or joins when it gets too deepSubqueries are super powerful in SQL. They let you break big problems into small steps. But keep it all in one query. Nesting gives you control over each layer.
Just remember. Start from inside, test often. Once you get nested subqueries? You’ll handle even the hardest SQL stuff. Like a pro!