Correlated subqueries

We go deeper. So far the subquery ran once and returned a ready result. A correlated subquery is different: it references a column from the outer query and runs anew for each row processed on the outside.

A reference to the outer row

1SELECT a.imie, a.nazwisko
2FROM autorzy AS a
3WHERE 3 < (
4  SELECT COUNT(*)
5  FROM ksiazki AS k
6  WHERE k.autor_id = a.id
7);

Notice the condition

k.autor_id = a.id

. The subquery knows

a.id

- the value from the current row of the outer query. For each author the Library counts their books and keeps only those with more than three.

How it works step by step

1. The outer query takes the first author.
2. The subquery counts that author's books.
3. The condition checks the result - the author stays or drops out.
4. The Library moves on to the next author and repeats everything from the start.

Plain or correlated?

• A plain subquery is self-contained - you can run it on its own.
• A correlated one makes no sense without the outer query - it needs the value

  a.id

  .

Correlated subqueries can be slower (they run many times), but they are remarkably powerful - they let you ask questions like "for each row, check something on the other side".

@name