Подзапрос, который зависит от внешней строки
До сих пор подзапрос выполнялся один раз и не зависел от внешнего запроса. Но подзапрос может использовать столбцы внешнего запроса.
Коррелированный подзапрос — подзапрос, который ссылается на столбцы внешнего запроса. Его результат свой для каждой строки внешнего запроса.
Сколько учеников в каждом классе?
SELECT c.name,
(SELECT COUNT(*) FROM students s WHERE s.class_id = c.id) AS students_count
FROM classes c
ORDER BY c.name;
Результат:
| name | students_count |
| 7А | 3 |
| 7Б | 4 |
| 8А | 4 |
| 9А | 0 |
Для каждой строки таблицы classes (псевдоним c) подзапрос считает учеников, у которых class_id равен c.id этой строки.
Обратите внимание на 9А: в нём 0 учеников, и он есть в результате. Внутренний JOIN с GROUP BY потерял бы его: у 9А нет ни одной пары с учениками.
Через LEFT JOIN и COUNT(s.id) 9А сохранится — это другой способ решить ту же задачу.
Всегда указывайте, из какой таблицы столбец
В коррелированных подзапросах давайте таблицам псевдонимы (c, s) и пишите их перед каждым столбцом. Вот что будет без них:
SELECT name,
(SELECT COUNT(*) FROM students WHERE class_id = id) AS students_count
FROM classes
ORDER BY name;
Результат:
| name | students_count |
| 7А | 1 |
| 7Б | 1 |
| 8А | 1 |
| 9А | 1 |
У каждого класса получилась единица, и SQLite не выдал ошибки. Столбец без псевдонима SQLite ищет сначала в таблицах самого подзапроса, и только потом во внешнем запросе.
В students есть свой столбец id. Поэтому class_id = id сравнило класс ученика с номером того же ученика, и подзапрос перестал зависеть от класса.