SQL and MySQL course Β· Module 8: Subqueries
The Query Labyrinth
In this lesson3
Welcome back! You stand at the gates of the Query Labyrinth - the most intricate part of the Great Library. Here I keep the scrolls that cannot be found with a single glance at a shelf. Sometimes, to find one book, you must first ask the Library another question, and only its answer points to the right shelf.
A question inside a question
In our world this trick is called a subquery. It is an ordinary SELECT query enclosed in round brackets and placed inside another query.
SELECT tytul
FROM ksiazki
WHERE cena > (SELECT AVG(cena) FROM ksiazki);First the Library runs the inner subquery - it computes the average price of all books. Only then does the outer query use that number to select books more expensive than the average.
Why descend into the Labyrinth?
- to compare a row with the result of a calculation (e.g. the average),
- to check whether a value belongs to a list returned by another query,
- to ask whether a related scroll exists on another shelf,
- to combine the results of two searches into one list.
What you will learn here
We will walk through every corridor of the Labyrinth: scalar subqueries, the IN operator, correlated subqueries, EXISTS / NOT EXISTS, derived tables in the FROM clause, and combining sets with UNION and UNION ALL.
Hold tight to Ariadne's thread - the logic of your query - and you will not get lost in the alleys. Let us begin!
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. What is a subquery in SQL?
2. In which brackets do we enclose a subquery in SQL?