Lesson 4: Subqueries — When One Query Isn’t Enough!

Lesson 4

Lesson 4: Subqueries — When One Query Isn’t Enough!

Welcome back, data adventurer! If you’ve made it this far, you’re probably thinking, “I’ve got this SQL stuff down.” But just when you thought you were the master of your database domain, PostgreSQL throws you a curveball: Subqueries. This is where things start to get interesting. Think of subqueries as secret missions — tiny queries hidden inside larger ones, doing the sneaky work behind the scenes.

What’s a Subquery, Anyway?

Imagine you’re at a restaurant. You’ve just ordered a pizza (the main query), but then you whisper to the waiter, “By the way, add extra olives if they’re available.” That’s your subquery! A subquery is a query nested inside another query, used to refine or extend the main query’s result.

Subqueries are perfect for those times when a single query just isn’t enough, like:

  • Getting top-performing knights (based on their quests completed).
  • Finding which dragons have burned more than three villages.
  • Identifying which knights need some, ahem, extra training.

Types of Subqueries

Subqueries come in different flavors, depending on how and where you use them. Let’s meet our key players:

  1. Single-Row Subqueries: Return one row. Think of it as a quick sneak peek.
  2. Multi-Row Subqueries: Return multiple rows. Great for comparing lists!
  3. Scalar Subqueries: Return a single value. Ideal for secret comparisons.
  4. Correlated Subqueries: These are the spies — they know what’s happening in the main query and adapt on the fly.

Now, let’s see some subqueries in action!

Using Subqueries in SELECT

Let’s start with a simple example. Suppose you have a table knights and you want to know which knight has completed the most missions:

SELECT name
FROM knights
WHERE missions_completed = (SELECT MAX(missions_completed) FROM knights);

This little subquery (SELECT MAX(missions_completed) FROM knights) goes off, does its thing, and comes back with the highest number of missions completed. It then tells the main query, “Only show me knights with this number.”

Subqueries in the FROM Clause

Now, let’s say we want to group knights by kingdom and see the average number of missions completed. A regular query might struggle, but a subquery in the FROM clause is here to save the day:

SELECT kingdom, AVG(missions_completed)
FROM (SELECT name, kingdom, missions_completed FROM knights) AS sub_knights
GROUP BY kingdom;

Here, our subquery acts like a temporary table, narrowing down the data before the main query swoops in.

Subqueries in WHERE Clauses

Subqueries are often used in the WHERE clause to set conditions. Imagine we want to find knights whose number of completed missions is greater than the average for all knights:

SELECT name
FROM knights
WHERE missions_completed > (SELECT AVG(missions_completed) FROM knights);

The subquery calculates the average, and then the main query says, “Show me knights who are above average!” (The overachievers, in other words.)

Correlated Subqueries: The Spies of SQL

Correlated subqueries are the sneakiest of them all. They run for every row in the main query and can reference columns from the main query itself. Let’s say we want to find out which knights have completed more missions than the average for their own kingdom:

SELECT name, kingdom
FROM knights AS k1
WHERE missions_completed > (SELECT AVG(missions_completed) 
                            FROM knights AS k2 
                            WHERE k2.kingdom = k1.kingdom);

Here, the subquery calculates the average number of missions for each kingdom dynamically. It’s like a spy in the main query, gathering intel and passing it along.

Subqueries in the SELECT Clause

Finally, let’s talk about subqueries in the SELECT clause. Imagine you want a report that shows each knight’s name and how they compare to the overall average missions completed:

SELECT name, 
       missions_completed, 
       (SELECT AVG(missions_completed) FROM knights) AS overall_average
FROM knights;

Now you have a clear comparison, and it’s all thanks to that sneaky little subquery.

Let’s Put It All Together: A Grand Subquery Showcase!

Here’s a scenario to show off everything we’ve learned:

You want to find the knights who have completed more missions than the overall average, but only if they belong to kingdoms where the average number of missions is greater than 3. Sounds complex? Here’s the magic:

SELECT name
FROM knights
WHERE missions_completed > (SELECT AVG(missions_completed) 
                            FROM knights) 
AND kingdom IN (SELECT kingdom 
                FROM knights 
                GROUP BY kingdom 
                HAVING AVG(missions_completed) > 3);

This beast of a query uses subqueries in both the WHERE clause and IN condition. It’s like commanding an entire army of mini-queries!

What Did We Learn Today?

Today we explored the world of subqueries:

  • Single-Row Subqueries: Return one row, used for precise comparisons.
  • Multi-Row Subqueries: Return multiple rows, perfect for lists.
  • Scalar Subqueries: Return a single value, used in comparisons.
  • Correlated Subqueries: Adapt to the main query dynamically.

What’s Next?

In the next lesson, we’ll dive into CTEs and Window Functions. Get ready to take your SQL skills to the next level!

Welcome to the world of subqueries. May your nested queries be neat and your results always accurate!