Cool! Now we'll practice correlated subqueries. Here's a brief reminder:
Correlated subqueries refer to the outer query. Take a look:
FROM Cats C
WHERE CatId IN
WHERE Wage > 5000 AND OwnedCatId = C.CatId)
In the above query we selected the IDs of cats that belong to owners who earn more than $5000. Note that the subquery refers to the table
Cats in the outer query: for each cat in the
Cats table the subquery is processed separately. The subquery can refer to tables in the outer query, but the outer query cannot refer to tables in the subquery. It's often helpful to give aliases to tables in both queries.
Just like uncorrelated subqueries, the correlated subqueries can be used in the
FROM clause of the query.
It is also possible to have a subquery in the
SELECT clause. Such a subquery has to return exactly one row and column. Here's an example:
(SELECT AVG(Age) FROM Cats C2 WHERE C2.Name = C1.Name)
FROM Cats C1