Опорная идея
Подзапрос может стоять и в списке колонок — чтобы добавить вычисляемое значение к каждой строке:
SELECT u.name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS orders_count FROM users u;
Для каждого пользователя подсчитывается число его заказов.
Оператор EXISTS проверяет, есть ли вообще строки, удовлетворяющие подзапросу — удобно для вопросов «есть ли хоть один»:
SELECT name FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id ); -- пользователи, у которых есть хотя бы один заказ
Когда подзапросов и JOIN-ов становится много, запрос можно разбить на читаемые шаги через конструкцию WITH (общее табличное выражение, CTE) — это уже следующий уровень, но знать о нём полезно: он делает сложные запросы понятными.