SQL and MySQL course Β· Module 8: Subqueries
Out of the Labyrinth - summary
In this lesson3
Well done! You have walked out of the Query Labyrinth without losing Ariadne's thread. Nested searches hold no more secrets for you. This was one of the hardest parts of the Great Library - many scribes lost themselves in its alleys for days on end. You walked through it with a steady step.
Remember the most important rule of the Labyrinth: always read a query from the inside out. First understand what the inner subquery computes or returns, and only then how the outer query uses it. With that habit even the most tangled nesting becomes clear.
What you have learned
- A subquery is a SELECT in round brackets, placed inside another query.
- A scalar subquery returns a single value - perfect for comparisons in
WHERE(e.g. with the averageAVG) or on theSELECTcolumn list. - The IN operator checks whether a value belongs to a list returned by a subquery;
NOT INworks the other way around. - A correlated subquery references a column from the outside and runs for each row.
- EXISTS returns true when the subquery finds at least one row; NOT EXISTS - when it finds none.
- A derived table is a subquery in
FROM. in MySQL it requires an alias. - UNION combines sets and removes duplicates, UNION ALL keeps all rows.
- Subqueries can be nested and combined with the
ANDcondition.
Map of the corridors
| Tool | The question it answers |
|---|---|
| Scalar subquery | "How does this row compare to a calculation?" |
IN + subquery | "Is the value on this list?" |
| Correlated subquery | "What on the other side matches this row?" |
EXISTS / NOT EXISTS | "Does a related scroll exist, or is it missing?" |
Derived table (FROM) | "Compute something on the result of another query" |
UNION / UNION ALL | "Combine two lists into one" |
What's next?
In the next location we will enter the Hall of Indexes, where you will learn to speed up searches - so the Library finds scrolls in the blink of an eye, even among hundreds of thousands of books. Onward!
Spotted a mistake in this lesson?