8 способов объединения (JOIN) таблиц в SQL. Часть 1
Можно смело сказать, что операция объединения (JOIN) является наиболее мощной функциональной особенностью языка SQL. Эта операция — предмет зависти для всех нереляционных СУБД, поскольку ее концепция очень проста, но при этом широко применима в случаях, когда нужно объединить два набора данных.
Простыми словами, объединение двух таблиц заключается в объединении каждой строки первой таблицы с каждой строкой второй таблицы, для которых истинно значение некоторого предиката. Иллюстрация из мастер-класса по SQL демонстрирует эту концепцию:
Обратите также внимание на следующую статью, посвященную использованию диаграмм Венна (Venn diagram) для объяснения операции JOIN.
На рисунке выше представлена схема операции внутреннего объединения (INNER JOIN) в сравнении с различными операциями внешнего объединения (OUTER JOIN), но это далеко не все возможные варианты. Далее мы рассмотрим каждый из них в отдельности.
Обратите внимание, когда в данной статье мы говорим «X следует перед Y», имеется в виду, что «X логически следует перед Y». То есть, оптимизатор СУБД может выполнить Y раньше, чем X, в целях обеспечения более высокой производительности при неизменном результате. Подробнее о синтаксическом и логическом порядке операций вы моете прочитать в следующей статье.
Итак, давайте последовательно рассмотрим все типы объединений!
Перекрестное объединение (CROSS JOIN)
CROSS JOIN является базовым вариантом объединения и представляет собой декартово произведение (Cartesian product). Эта операция просто объединяет каждую строку первой таблицы с каждой строкой второй таблицы. Лучший пример, иллюстрирующий декартово произведение, представлен в Википедии. В этом примере мы получаем колоду карт, выполнив «перекрестное объединение» таблицы достоинств и таблицы мастей.
В реальных сценариях операция CROSS JOIN может быть очень полезна при создании отчетов. Например, мы можем сгенерировать набор дат (например, дни в месяце) (days) и выполнить перекрестное объединение со всеми отделами (departments), имеющимися в базе данных. В результате мы получим полную таблицу день/отдел. Используя синтаксис PostgreSQL:
Представим себе, что мы имеем следующие данные:
Результат операции CROSS JOIN будет выглядеть следующим образом:
Теперь для каждой комбинации день/отдел мы можем вычислить дневную выручку для данного отдела или другие аналогичные показатели.
Свойства
Как мы уже сказали, операция CROSS JOIN представляет собой декартово произведение. Соответственно, в математической нотации для описания данной операции используется знак умножения: A × B, или в нашем случае days × departments.
Как и в случае «обычного» арифметического умножения, если одна из двух таблиц пустая (имеет нулевой размер), результат также будет пустым. Это абсолютно логично. Если мы объединим 31 день и 0 отделов, мы получим 0 комбинаций день/отдел. Аналогично, если мы объединим пустой диапазон дат с любым количеством отделов, мы также получим 0 комбинаций день/отдел. Другими словами:
Альтернативный синтаксис
До того, как синтаксис операции JOIN был стандартизирован ANSI, чтобы реализовать CROSS JOIN, программисты просто использовали список разделенных запятыми таблиц в предложении FROM. Рассмотренный выше запрос эквивалентен следующему:
В общем случае для выполнения перекрестного объединения настоятельно рекомендуется использовать ключевые слова CROSS JOIN вместо альтернативного синтаксиса. Благодаря этому, другой программист сможет легко понять назначение данного фрагмента кода! Кроме того, использование альтернативного синтаксиса на основе списка разделенных запятыми таблиц чревато появлением ошибок, например, может произойти ненамеренное перекрестное объединение. Нам же не нужны такие проблемы!
Внутреннее объединение (INNER JOIN) или тета-объединение (THETA JOIN)
Развивая идею предыдущей операции CROSS JOIN, операция INNER JOIN (или просто JOIN, иногда также THETA JOIN) позволяет выполнять фильтрацию результата декартова произведения на основе некоторого предиката. Как правило, мы помещаем этот предикат в предложение ON. Таким образом, запрос принимает следующий вид:
В большинстве СУБД ключевое слово INNER является необязательным, поэтому мы просто не указываем его.
Операция INNER JOIN позволяет нам использовать произвольные предикаты в предложении ON, что опять же очень удобно при создании отчетов. Аналогично CROSS JOIN мы объединяем все дни со всеми отделами, но потом оставляем только те комбинации день/отдел, для которых данный отдел уже существовал в данный день.
Используем те же исходные данные:
Получим следующий результат:
Результат операции содержит данные, начиная с 10 января. Более ранние даты были отфильтрованы.
Свойства
Операция INNER JOIN представляет собой операцию CROSS JOIN с фильтрацией. Это означает, что если одна из таблиц пустая, то результат также гарантированно будет пустым. По причине наличия предиката, результат операции INNER JOIN может быть меньшего объема, чем результат операции CROSS JOIN. Другими словами:
Альтернативный синтаксис
Несмотря на то, что предложение ON является обязательным для операции INNER JOIN, мы не обязаны указывать в нем предикат (хотя это крайне желательно в целях улучшения читаемости). Рассмотренный выше запрос эквивалентен следующему:
Безусловно, это просто запутывание кода, но ведь у нас могут быть свои причины, не так ли? Сделав еще один шаг, мы можем написать следующий запрос, который также является эквивалентным, поскольку большинство оптимизаторов способны распознать равнозначность и выполнить INNER JOIN:
Как мы уже говорили, CROSS JOIN это лишь удобный синтаксис для списка разделенных запятыми таблиц. Во фрагменте кода, представленном ниже, мы также используем предложение WHERE, чтобы сформировать запрос, которым программисты часто пользовались до того, как синтаксис JOIN был стандартизирован:
Все эти варианты альтернативного синтаксиса выполняют одну и ту же задачу, как правило, без потери производительности. Однако очевидно, что все они значительно хуже читаются по сравнению со стандартным синтаксисом INNER JOIN.
Объединение на основе равенства (EQUI JOIN)
Иногда в литературе встречается термин EQUI JOIN. На самом деле, «EQUI» не является ключевым словом SQL, а просто обозначает специальный вариант записи особого случая операции INNER JOIN.
Следует отметить, что не совсем правомерно называть EQUI JOIN особым случаем, поскольку эту операцию мы выполняем чаще всего в SQL и OLTP приложениях, когда просто объединяем таблицы на основе отношения первичного/внешнего ключа. Например:
Представленный выше запрос извлекает всех актеров и фильмы, в которых они снимались. В нем присутствуют две операции INNER JOIN. Первая из них объединяет таблицу актеров actor и соответствующие записи из таблицы film_actor, содержащей информацию об отношениях фильм/актер (поскольку каждый актер может играть во множестве фильмов, а в каждом фильме может играть множество актеров). Вторая операция INNER JOIN выполняет объединение с таблицей film, содержащей информацию о фильмах.
Свойства
Данная операция имеет те же свойства, что и «обычная» операция INNER JOIN. То есть EQUI JOIN также является декартовым произведением (CROSS JOIN) с отфильтрованным результатом. В частности, в нашем случае результат содержит только те комбинации актер/фильм, для которых данный актер действительно играл в данном фильме. Таким образом, мы снова имеем соотношение:
Объем результата может быть равен полному декартову произведению таблиц actor и film только в том случае, если каждый актер играл в каждом фильме, что маловероятно.
Альтернативный синтаксис: USING
Опять же, мы могли бы записать операцию EQUI JOIN, используя CROSS JOIN или список разделенных запятыми таблиц, но это уже не интересно. Значительно больший интерес представляют два варианта альтернативного синтаксиса, представленные ниже, один из которых является очень полезным.
Предложение USING заменяет предложение ON и позволяет указать набор столбцов, которые должны присутствовать в обеих объединяемых таблицах. Если наша база данных была хорошо спроектирована (как, например, база данных Sakila), то есть, если каждый внешний ключ имеет такое же имя, как и соответствующий первичный ключ (например, actor.actor_id = film_actor.actor_id), тогда мы можем использовать предложение USING для реализации операции EQUI JOIN, как минимум, в следующих СУБД:
Следующие СУБД, к сожалению, не поддерживают данный синтаксис:
- Access
- Cubrid
- DB2
- H2
- HANA
- Informix
- SQL Server
- Sybase ASE
- Sybase SQL Anywhere
Запрос с предложением USING (почти) идентичен запросу с предложением ON, однако значительно более удобен для написания и восприятия. Мы сказали «почти», потому что согласно спецификации некоторых СУБД (и стандарту SQL) столбец, используемый в предложении USING, не должен иметь квалификатор. Например:
Безусловно, этот синтаксис также имеет свои ограничения. Иногда в таблице может быть внешний ключ, имя которого не соответствует первичному ключу. Например:
Если мы хотим выполнить объединение по original_language_id, нам придется использовать предложение ON.
Альтернативный синтаксис: Естественное объединение (NATURAL JOIN)
Более экстремальным и значительно менее полезным вариантом синтаксиса операции EQUI JOIN является синтаксис на основе предложения NATURAL JOIN. Рассмотренный выше синтаксис на основе USING можно «улучшить», заменив USING на NATURAL JOIN следующим образом:
Обратите внимание, в этом запросе нет необходимости указывать какие-либо критерии объединения, поскольку предложение NATURAL JOIN автоматически определяет столбцы, имеющие одинаковые имена в обеих объединяемых таблица, и помещает их в «скрытое» предложение USING. Если первичные и внешние ключи имеют одинаковые имена, этот подход может показаться полезным, однако это не так.
В базе данных Sakila, каждая таблица имеет столбец last_update, который автоматически используется предложением NATURAL JOIN. Таким образом, запрос NATURAL JOIN эквивалентен следующему запросу, который, конечно же, не имеет никакого смысла:
Итак, сразу же забудьте о NATURAL JOIN и никогда не используйте этот вариант (за исключением очень редких случаев, таких как объединение диагностических представлений Oracle, например, v$sql NATURAL JOIN v$sql_plan, в целях специализированной аналитики).
Внешнее объединение (OUTER JOIN)
Мы рассмотрели операцию INNER JOIN, возвращающую только те комбинации строк левой/правой таблицы, для которых значение предиката в предложении ON является истинным.
Операция OUTER JOIN позволяет нам включить в результат строки одной таблицы, для которых не были найдены соответствующие строки в другой таблице.
Левое внешнее объединение (LEFT OUTER JOIN)
Давайте вернемся к примеру с датами и отделами:
Ключевое слово «OUTER» является необязательным, поэтому мы его не указываем.
Этот запрос отличается от подобного запроса INNER JOIN лишь тем, что всегда будет возвращать хотя бы одну строку для каждого дня, даже если в данный день еще не существовало ни одного отдела. В частности, в нашем примере все отделы были созданы не ранее 10 января, но запрос все равно вернет строки, соответствующие 1–9 января.
Кроме строк, которые мы получили бы с помощью запроса INNER JOIN, в результате запроса LEFT OUTER JOIN также присутствуют строки, соответствующие 1–9 января, с пустыми (NULL) значениями отделов:
Как видите, каждый день хотя бы один раз присутствует в результате запроса. LEFT OUTER JOIN выполняет данную операцию для левой таблицы, то есть возвращает все строки левой таблицы.
Формально, операцию LEFT OUTER JOIN можно выразить операцией INNER JOIN с предложением UNION:
Мы обсудим NOT EXISTS далее в этой статье, когда будем рассматривать операцию SEMI JOIN.
Правое внешнее объединение (RIGHT OUTER JOIN)
Операция RIGHT OUTER JOIN выполняет ту же задачу, что и LEFT OUTER JOIN, но для правой таблицы, то есть возвращает в результате все строки правой таблицы. Немного модифицируем наши данные, добавив пару отделов:
Новые отделы 4 и 5 не попали бы в результат запроса INNER JOIN, поскольку были созданы после 31 января. Однако эти отделы появятся в результате запроса RIGHT OUTER JOIN, поскольку эта операция возвращает все строки правой таблицы.
Выполним следующий запрос:
Получим следующий результат:
В большинстве случаев (я еще не сталкивался с ситуацией, для которой это утверждение не верно), выражение LEFT OUTER JOIN можно преобразовать в эквивалентное выражение RIGHT OUTER JOIN, и наоборот. Поскольку RIGHT OUTER JOIN обеспечивает меньшее удобство восприятия, большинство программистов используют только LEFT OUTER JOIN.
Полное внешнее объединение (FULL OUTER JOIN)
Существует также операция FULL OUTER JOIN, которая возвращает в результате все строки как левой, так и правой таблицы. Для нашего примера это означает, что каждый день и каждый отдел хотя бы один раз появляются в результате запроса.
Используем те же данные:
Выполним следующий запрос:
Получим следующий результат:
Формально, операцию FULL OUTER JOIN можно выразить операцией INNER JOIN с предложением UNION:
Альтернативный синтаксис: Внешнее объединение на основе равенства (EQUI OUTER JOIN)
Рассмотренные выше операции опять же представляют собой объединения типа «декартово произведение с фильтрацией». Однако более распространенным является подход EQUI OUTER JOIN, в рамках которого мы выполняем объединение на основе отношения первичного/внешнего ключа. Используем для примера базу данных Sakila. Некоторые актеры не снялись ни в одном фильме. Мы можем извлечь их следующим образом:
В результате этого запроса каждый актер будет присутствовать хотя бы один раз, независимо от того, принимал ли он участие в каком-либо фильме. Если мы также хотим извлечь все фильмы, в которых не снимался ни один из данных актеров, мы можем применить FULL OUTER JOIN:
Безусловно, в качестве альтернативы можно было бы использовать NATURAL LEFT JOIN, NATURAL RIGHT JOIN, NATURAL FULL JOIN, но, как мы уже говорили ранее, в таком случае в объединении автоматически был бы учтен столбец last_update, присутствующий во всех таблицах базы данных Sakila (т.е. USING (…, last_update)), что лишает операцию всякого смысла.
Альтернативный синтаксис: Внешнее объединение (OUTER JOIN) в стиле Oracle и SQL Server
До введения стандартного синтаксиса СУБД Oracle и SQL Server поддерживали операцию внешнего объединения в следующем виде:
Можно смело сказать, что этот синтаксис является устаревшим.
Разработчики SQL Server поступили правильно, вначале объявив этот синтаксис нежелательным, и в дальнейшем отказавшись от него. Oracle по-прежнему поддерживает его для обратной совместимости.
Нет никаких аргументов в пользу данного альтернативного синтаксиса. Используйте вместо него стандартный синтаксис ANSI.
Внешнее объединение с разделением (PARTITIONED OUTER JOIN)
Эта операция поддерживается только Oracle. На самом деле, просто удивительно, что другие СУБД до сих пор не реализовали ее. Помните операцию CROSS JOIN, которую мы использовали, чтобы получить все комбинации день/отдел? Так вот, иногда мы хотим получить следующий результат: все комбинации, а также, если выполняется условие, поместить в данную строку соответствующее значение.
Эту операцию трудно объяснить словами. Намного легче сделать это на примере. Ниже представлен запрос, использующий синтаксис Oracle:
Предложение PARTITION BY используется в различных контекстах для решения различных задач (например, для реализации оконных функций (window function)). В нашем случае PARTITION BY означает, что мы «разделяем» наши данные по значениям столбца departments.department, создавая таким образом «подгруппу» для каждого отдела. Затем каждая «подгруппа» получает копию всех дней, независимо от того, выполняется ли условие предиката (в отличие от обычной операции LEFT OUTER JOIN, в результате которой, часть дней имели пустые значения отделов). Представленный выше запрос даст следующий результат:
Как видите, мы имеем 5 «подгрупп», соответствующих 5 отделам. Каждая «подгруппа» объединяет данный отдел с каждым днем, но в отличие от CROSS JOIN, мы получаем результат LEFT OUTER JOIN .. ON .. в том случае, когда выполняется условие предиката. Это действительно полезная функциональность для создания отчетов в Oracle!
Источник
