Сложные SQL-запросы: объединения таблиц (JOIN), группировка (GROUP BY, HAVING) и подзапросы
Продвинутая выборка данных в СУБД: INNER, LEFT, RIGHT и FULL JOIN, многомерная агрегация с GROUP BY и HAVING, коррелированные подзапросы и CTE.
Поскольку нормализованная база данных разделена на множество связанных таблиц, извлечение сводной информации требует их объединения. Фундаментальной математической операцией является Декартово произведение (CROSS JOIN), сопоставляющее каждую строку первой таблицы с каждой строкой второй (если в таблице A 100 строк, а в B 200, результат составит 20 000 строк).
Оператор INNER JOIN (внутреннее объединение) накладывает условие соответствия ключей (предикат ON table1.id = table2.foreignid). В результирующую выборку попадают только те строки, для которых найдено строгое совпадение в обеих таблицах. Если у студента не назначена группа, или у группы нет ни одного студента, эти записи в результат INNER JOIN не войдут.
INNER JOIN
Предикат ON
Когда необходимо сохранить строки из одной таблицы, даже если для них нет соответствующих записей в другой, применяются внешние объединения (Outer Joins):
1. LEFT JOIN (или LEFT OUTER JOIN): возвращает абсолютно все строки из левой таблицы. Если для строки левой таблицы не нашлось соответствия в правой, столбцы правой таблицы заполняются значениями NULL. Классический сценарий: 'Найти всех клиентов, включая тех, кто еще не сделал ни одного заказа'.
2. RIGHT JOIN: симметричен LEFT JOIN, сохраняет все строки из правой таблицы.
3. FULL OUTER JOIN: объединяет результаты левого и правого соединений, возвращая все строки из обеих таблиц и подставляя NULL там, где совпадений не обнаружено.
Шаблон анти-соединения (Anti-Join)
Конструкция 'LEFT JOIN ... WHERE righttable.id IS NULL' — это стандартный высокопроизводительный шаблон для поиска неактивных записей, отсутствующих связей и потерянных ссылок.
Агрегатные функции (COUNT, SUM, AVG, MIN, MAX) вычисляют единое итоговое значение по множеству строк. Конструкция GROUP BY разделяет исходный набор данных на группы по указанным колонкам, применяя агрегацию к каждой группе отдельно.
Критически важно понимать порядок выполнения операций в SQL:
1. FROM / JOIN: выборка и объединение источников данных.
2. WHERE: фильтрация индивидуальных строк до группировки. В WHERE нельзя использовать агрегатные функции (например, WHERE SUM(price) > 1000 приведет к синтаксической ошибке).
3. GROUP BY: формирование групп строк.
4. HAVING: фильтрация уже сформированных групп после применения агрегатных функций.
5. SELECT: формирование столбцов вывода.
6. ORDER BY: сортировка результата.
Подзапрос (Subquery) — это запрос SELECT, вложенный внутрь другого запроса. Подзапросы могут использоваться в секциях WHERE (с операторами IN, EXISTS, ANY, ALL), FROM (как временная таблица) или SELECT (скалярные подзапросы).
Однако глубоко вложенные подзапросы быстро становятся нечитаемыми. Современный стандарт SQL предлагает элегантную альтернативу — Common Table Expressions (CTE), объявляемые ключевым словом WITH. CTE создают именованные временные результирующие наборы, к которым можно многократно обращаться в основном запросе.