Что такое подзапрос (subquery) в SQL и для чего он используется?

Давай разберём эту штуку, которая в SQL называется подзапросом. Представь, что ты спрашиваешь у друга: «Слушай, а кто из наших получает зарплату выше средней?». А он тебе в ответ: «Ну, сначала посчитай среднюю зарплату, а потом сравни с ней всех». Вот этот «сначала посчитай» — это и есть подзапрос, ёпта! Запрос внутри запроса, как матрёшка, блядь.

Что это вообще такое? Это когда ты пишешь один SQL-запрос, а внутри него, в скобках, сидит ещё один, маленький и несчастный. Результат этого внутреннего запроса (одно число, список или целую таблицу) внешний запрос использует, чтобы свою грязную работу сделать.

Основные разновидности этой хуйни:

1. Скалярный (возвращает одно значение, одну клеточку). Допустим, ты хочешь найти всех, кто получает больше среднего. Как узнать среднее? Вот так:

   SELECT AVG(salary) FROM employees;

   А теперь вставь это прямо в условие, как палец в розетку:

   SELECT name
   FROM employees
   WHERE salary > (SELECT AVG(salary) FROM employees); -- Вот он, сука, сидит в скобках!

   Внешний запрос спрашивает: «Зарплата больше ЧЕГО?». А внутренний ему шепчет: «Больше 100500, мудак». И всё работает.

2. Подзапрос с IN (возвращает столбик значений). Нужны товары только из активных категорий. Сначала выцепим ID этих активных категорий:

   SELECT id FROM categories WHERE active = TRUE;

   А теперь скажем основному запросу: «Дай мне всё, у чего category_id ВХОДИТ В ТОТ СПИСОК, который я только что получил».

   SELECT * FROM products
   WHERE category_id IN (SELECT id FROM categories WHERE active = TRUE);

   Просто и элегантно, как удар кирпичом по лбу.

3. Коррелированный подзапрос (самый хитрый, блядь). Вот это уже высший пилотаж. Этот подзапрос не самостоятельный уёбок — он ЗАВИСИТ от каждой строчки внешнего запроса. Как будто для каждого сотрудника ты задаёшь отдельный вопрос. «Найди всех сотрудников, которые хоть раз были руководителями активных проектов».

   SELECT e.name
   FROM employees e
   WHERE EXISTS (
       SELECT 1 FROM projects p
       WHERE p.lead_id = e.id AND p.status = 'ACTIVE' -- Смотри, он ссылается на e.id из внешней таблицы!
   );

   Для каждой строки из employees подзапрос лезет в projects и проверяет: «А есть ли хоть один активный проект, где этот чувак — лид?». Если есть (EXISTS) — строка проходит. Хитрая жопа, да? Сильно может тормозить на больших данных, имей в виду.

4. Подзапрос прямо в FROM (делаем виртуальную таблицу на лету). Иногда нужно сначала сгруппировать что-то, а потом к результату присоединить другие данные. Например, получим среднюю зарплату по отделам, а потом прицепим к этому названия отделов. Сначала создаём временную таблицу со средними зарплатами:

   SELECT department_id, AVG(salary) as avg_sal
   FROM employees
   GROUP BY department_id;

   А теперь говорим: «Эй, SQL, считай, что результат этого запроса — это новая таблица, назови её dept_stats и присоедини к departments».

   SELECT d.name as dept_name, ds.avg_sal
   FROM (
       SELECT department_id, AVG(salary) as avg_sal
       FROM employees
       GROUP BY department_id
   ) AS dept_stats -- Вот эта наша виртуальная таблица
   JOIN departments d ON d.id = dept_stats.department_id;

Итог и важная мысль, блядь: Подзапросы — это мощно, это гибко, иногда без них нихуя не сделаешь. НО! Часто ту же самую задачу можно решить через обычные JOIN. И как правило, JOIN оптимизатору базы данных переваривать проще, и работает это быстрее. Поэтому если видишь, что подзапрос в WHERE можно легко заменить на JOIN в FROM — делай так, не будь мудаком. Используй подзапросы точечно, где они реально упрощают логику, а не там, где можно обойтись более прямым путём. Всё, чих-пых тебя в сраку, иди практикуйся.