What are nested queries and why do we need them?

Now you're ready for the next step — working with nested queries in SQL. Today, we'll break down what they are, why we need them, what types of nested queries exist, and why they're actually useful in real life.

Nested queries (or subqueries) are SQL queries that are used inside other SQL queries. It's kinda like a matryoshka doll or a cabbage: there's an outer query, and inside it is another, smaller query. The subquery runs first, and its result is used by the outer query (sometimes called the "main" query).

Let's figure it out with an example:

Example 1: How it works

We have a table called students with the following data:

And also a table called groups, where we store info about group names:

If we want to find out the names of the groups where students study, we can use a nested query:

SELECT name
FROM groups 
WHERE id IN (
    SELECT group_id 
    FROM students 
    WHERE age > 21
);

What's going on here?

The nested query:

SELECT group_id 
FROM students 
WHERE age > 21

This query selects group_id for all students older than 21. The result: a list of group IDs, for example [2, 3].

The main query:

SELECT name 
FROM groups 
WHERE id IN ([subquery results])

This query uses the results of the subquery and returns the names of the groups with id equal to 2 or 3.

Result:

Physics class
Literature class

Still not clear? Makes sense. But don't worry, we're gonna break it all down right now.

Let's start with a simple idea — the result of a SELECT query is basically a virtual table. Seriously, it's got columns, it's got rows. Why not call it a table?

And if the result of a query is a table, you can use it wherever you use real tables: in a JOIN, for example, or in more complex stuff.

It doesn't have a name, and that's a problem. But columns/expressions don't have names either, and we solve that by giving them aliases. You can do the same with virtual tables.

More on that in the next lectures — no spoilers yet :P

Why nested queries are useful

1. They make tough tasks easier. Sometimes one table just doesn't have all the info you want. Subqueries let you split your query into two steps: first you get an intermediate result, then you use it to get the final data.

2. Working with intermediate results. Nested queries are handy when you need to do extra calculations before processing the data. For example, finding a minimum value or calculating a sum.

3. Better code readability. Nested queries make your code more structured, especially if you're dealing with big tables and complicated logic.

Main types of nested queries

You can use nested queries in different parts of an SQL query. Depending on where you write them, there are a few types.

1. Subqueries in SELECT. The subquery is in the column list and is used to calculate values. This is handy, for example, for adding a new column to your results.

Example — let's add a column with the max age among students:

SELECT name, age,
       (SELECT MAX(age) FROM students) AS max_age 
FROM students;

Result:

2. Subqueries in FROM. The subquery is used as a temporary table. This is useful if you need to aggregate or transform data first.

Example — calculating the average age of students in each group:

SELECT tmp.group_id, tmp.avg_age
FROM (
    SELECT group_id, AVG(age) AS avg_age
    FROM students
    GROUP BY group_id
) AS tmp -- Giving the temporary table the alias tmp 
WHERE tmp.avg_age > 21;

Result:

3. Subqueries in WHERE and HAVING. Subqueries can be used as a condition to filter rows. This is often used to check
4. if records exist or to compare values.

Example — students who are older than the average age:

SELECT name, age
FROM students
WHERE age > (
    SELECT AVG(age) 
    FROM students
);

Result:

Advantages of using nested queries

More flexibility: nested queries let you work with more complex data structures.

Breaking tasks into steps: you can split your logic into subqueries, which makes your code easier to read.

Access to intermediate data: you can process data "on the fly" without having to create temp tables in your database.