В известной книге "SQL. Полное руководство", 3 изд [2019], авторы Грофф, Вайнберг, Оппель в главе, которая посвящена подзапросам, есть такое заключение:
имеется много запросов с подзапросами, которые нельзя выразить в
виде эквивалентного соединения
В книге есть пример такого запроса:
И дано пояснение:
В данном случае внутренний запрос является итоговым, а внешний - нет, поэтому из этих двух запросов нельзя создать соединение
Вообще на практике объединять (соединять) в SQL мы можем почти всё что угодно, поэтому тут интересно поэкспериментировать. Для выполнения примеров используем СУБД SQLite 3.33.0.
Создадим тестовую таблицу SALESREPS. Для проверки результатов достаточно нескольких строк.
Посмотрим что на этих данных выдаст исходный запрос из книги.
В результат получили двух человек, у которых квота больше средней квоты по всем записям.
Известно, что результат запроса SQL даёт таблицу. В нашем случае эта таблица состоит из единственной строки и единственного значения средней квоты.
Мы можем включить эту результирующую таблицу в CROSS JOIN, который даст нам объединение каждой строки левой таблицы с каждой строкой правой таблицы. Левой таблицей считается таблица, указанная слева от JOIN, соответственно правой та что справа от JOIN.
Получили такой же результат:
Можно немного придраться и сказать что CROSS JOIN это декартово произведение таблиц, не совсем объединение. Тогда мы можем с тем же успехом использовать внутренне объединение INNER JOIN. Дополнительно укажем истинность условия объединения строк таблицы ON TRUE в четвёртой строке команды, хотя это не обязательно.
Результат снова верный:
Интересно будет применить для решения AVG в качестве оконной функции и редкий в практическом использовании вариант натурального объединения таблиц NATURAL JOIN. Вопрос эффективности использования такой логики пока выносим из обсуждения, рассмотрим её как вариант решения.
Натуральное объединение соединяет таблицы по данным столбцов с одинаковыми именами. В запросе ниже соединение будет выполнено по столбцам NAME и AGE.
Более наглядным последний вариант запроса можно сделать, если вынести вложенный запрос в общее табличное выражение:
В последних двух примерах также получим верный результат:
Какой можно сделать вывод из таких упражнений?
В исходном запросе действительно нет возможности соединять таблицу основного запроса и результирующую таблицу подзапроса по некоторым значениям. Но у нас есть интересная возможность попробовать другую логику объединения.
Автор: Виктор Щупоченко