Объединение наборов данных

 

Как было показано в примере использования секции WHERE для выборки из двух таблиц (см. подраздел «Выбор источников в секции FROM»), существует возможность выборки данных из разных источников с объединением их полей. В SQL этот процесс формально называется объединением (join).

В результате объединения двух или более наборов данных создается новый набор записей, состоящих из всех полей исходных наборов. Базовый вариант объединения представляет собой декартово произведение, то есть совокупность всех возможных комбинаций двух наборов. Далее из этого произведения отбирается часть записей по критериям, заданным в секции JOIN.

Существуют три разновидности объединений.

  • Перекрестные объединения (CROSS JOIN). Декартово (перекрестное) произведение двух наборов данных. Произведение не определяет отношений между наборами, а лишь содержит все возможные комбинации записей объединяемых наборов.
  • Внутренние объединения (INNER JOIN). Подмножество декартова произведения двух наборов данных с критерием, используемым для объединения записей. Критерий возвращает логическую величину — признак вхождения записи в объединенный набор.
  • Внешние объединения (OUTER JOIN). Как и внутренние объединения, содержат критерий объединения записей, но обязательно возвращают минимум один экземпляр каждой записи заданного набора. Это может быть левый набор (источник данных слева от ключевого слова JOIN), правый набор (источник данных справа от ключевого слова JOIN) или оба набора в зависимости от конкретной разновидности внешнего объединения. Пустые поля в тех частях записей, которые не отвечают критерию объединения, содержат NULL.

 

Перекрестные объединения

Результат перекрестного объединения принципиально не отличается от перечисления источников через запятую. Следовательно, в команде выборки с перекрестным объединением практически всегда должна присутствовать секция WHERE, уточняющая связи между объединенными наборами данных. В листинге 4.35 приведен запрос из листинга 4.27, в котором перечисление источников заменено формальным синтаксисом JOIN.

Листинг 4.35. Простой перекрестный запрос

booktown=# SELECT b.id, title, a.id, last_name

booktown-# FROM books AS b CROSS JOIN authors AS a

booktown-# WHERE b.author_id = a.id;

id | title | id | last_name

190 | Little Women | 16 | Alcott

156 | The Tell-Tale Heart | 115 | Рое

41472 | Practical PostgreSQL | 1212 | Worsley

2038 | Dynamic Anatomy | 1644 | Hogarth

1608 | The Cat in the Hat | 1809 | Geisel

1590 |Bartholomew and the Oobleck | 1809 | Geisel

4513 | Dune | 1866 | Herbert

4267 | 2001:Space Odyssey | 2001 | Clarke

1501 | Goodnight Moon | 2031 | Brown

7808 | The Shining | 4156 | King

41473 | Programming Python | 7805 | Lutz

41477 | Learning Python | 7805 | Lutz

41478 | Perl Cookbook | 7806 | Christiansen

25908 | Franklin in the Dark | 15990 | Bourgeois

1234 | The Velveteen Rabbit | 25041 | Bianco

(15 rows)

Синтаксис CROSS JOIN всего лишь более формально выражает отношения между двумя наборами данных. Между синтаксисом CROSS JOIN и простым перечислением таблиц через запятую нет никаких функциональных различий.

 

Внутренние и внешние объединения

На практике чаще используются внутренние и внешние объединения, при которых секция JOIN обязательно содержит критерий, уточняющий связи между объединяемыми наборами данных. Синтаксис внутренних и внешних объединений:

источник! [ NATURAL ] тип_объединения источник2

[ ON ( условие [. …] ) I USING ( поле [, …] ) ]

  • источник! Первый из объединяемых наборов данных (имя таблицы или подзапрос).
  • [ NATURAL ]. Два набора данных объединяются по равным значениям одноименных полей (например, если обе таблицы содержат поле с именем id, то объединяются записи с совпадающими значениями полей id). При наличии ключевого слова NATURAL учитываются синонимы полей (если они были назначены), а секции ON и USING становятся не только ненужными, но и недопустимыми.
  • тип_объединепия. В данном контексте допустимы следующие типы объединений: [INNER] JOIN (то есть JOIN без уточнения подразумевает INNER JOIN), LEFT [OUTER] JOIN, RIGHT [OUTER] JOIN и FULL [OUTER] JOIN.
  • источник2. Второй из объединяемых наборов данных (имя таблицы или подзапрос).
  • ON ( условие [. … ] ). Отношение между источниками. В секции ON можно задать произвольный критерий по аналогии с тем, как задаются условия в секции WHERE. В критерии могут использоваться синонимы таблиц и полей.
  • USING ( поле [, … ]). Одноименные поля источников, по совпадающим значениям которых производится объединение. В отличие от NATURAL JOIN позволяет ограничиться некоторыми одноименными полями, тогда как NATURAL проводит объединение по всем одноименным полям. По аналогии с NATURAL в параметрах секции USING учитываются синонимы полей.

 

Внутренние объединения

Конструкция INNER JOIN была включена в стандарт SQL92 для того, чтобы условия объединения источников данных (условия JOIN) можно было отличить от условий принадлежности записей к итоговому набору (условия WHERE). Рассмотрим две команды SELECT, приведенные в листинге 4.36.

Листинг 4.36. Сравнение команд INNER JOIN и WHERE

booktown=# SELECT title, last_name, first_name

booktown-# FROM books, authors

booktown-# WHERE (books.author_id = authors.id)

booktown-# AND last_name = ‘Geisel’:

title last_name | first_name

The Cat in the Hat | Geisel | Theodor Seuss

Bartholomew and the Oobleck | Geisel | Theodor Seuss

(2 rows)

booktown=# SELECT title, last_name, first_name

booktown-# FROM books AS b INNER JOIN authors AS a

booktown-# ON (b.author_id = a.id)

Dooktown-# WHERE last_name = ‘Geisel’;

title | last_name | first_name

The Cat in the Hat | Geisel | Theodor Seuss

Bartholomew and the Oobleck | Geisel | Theodor Seuss

(2 rows)

Две синтаксические формы в листинге 4.36 функционально идентичны и возвращают одинаковые результаты. Синтаксис INNER JOIN позволяет отделить критерий связи источников от критерия выбора записей, поскольку связи определяются только в секции ON. Это существенно упрощает чтение и модификацию запросов, поскольку программисту не приходится разбираться в смысле каждого условия в секции WHERE.

Обратите внимание: во втором запросе продемонстрировано назначение синонимов а и b в секции ON для таблиц books и authors соответственно. Подобное использование синонимов в секции ON абсолютно законно, более того — часто оно предпочтительно, поскольку программа становится более наглядной.

В случае простых объединений по совпадающим значениям вместо ON иногда бывает удобнее использовать секции USING и NATURAL. Впрочем, они применимы лишь к наборам данным, содержащим одноименные поля. Если поля, по которым устанавливается связь между наборами, имеют разные имена, все равно остается возможность использования секций USING и NATURAL благодаря назначению синонимов полей (листинг 4.37).

Листинг 4.37. Секции NATURAL и USING

booktown=# SELECT title. last_name, first_name

booktown-# FROM books INNER JOIN authors AS a (author_id)

booktown-# USING (author_id)

booktown-# WHERE last_name = ‘Geisel’;

title | last_name | first_name

The Cat in the Hat | Geisel | Theodor Seuss

Bartholomew and the Oobleck | Geisel | Theodor Seuss

(2 rows)

booktown=# SELECT title, last_name, first_name

booktown-# FROM books NATURAL INNER JOIN authors AS a (author_id)

booktown-# WHERE lastjiame = ‘Geisel’;

title | last_name | first_name

The Cat in the Hat | Geisel | Theodor Seuss

Bartholomew and the Oobleck | Geisel | Theodor Seuss

(2 rows)

Первая команда SELECT в листинге4.37 назначает синоним authorjd первому полю таблицы authors (хотя в действительности это поле называется id). Идентификатор author_id передается в секцию USING, после чего PostgreSQL ищет в каждом наборе идентификатор поля с этим именем для объединения записей.

Внутренние объединения часто применяются на практике, но в некоторых ситуациях требуется выборка всех необходимых данных с использованием внешнего объединения. Чтобы попять суть различий между внутренними и внешними объединениями, достаточно разобраться, что происходит с записями, не входящими в установленную связь.

При внутреннем объединении все записи, для которых не находится соответствующего значения в других наборах (заданных при помощи ON или USING), просто игнорируются.

 

Внешние объединения

С другой стороны, внешнее объединение может сохранить записи, для которых не находится соответствия в других наборах. В этом случае недостающие поля заполняются значениями NULL. Решение о том, войдет ли такая запись во внешнее объединение, зависит от того, в каком из объединяемых наборов отсутствуют данные, и от типа внешнего объединения.

Существуют три разновидности внешних объединений.

  • Левое внешнее объединение. Всегда содержит как минимум один экземпляр каждой записи из набора, указанного слева от ключевого слова JOIN. Отсутствующие поля из правого набора заполняются значениями NULL.
  • Правое внешнее объединение. Всегда содержит как минимум один экземпляр каждой записи из набора, указанного справа от ключевого слова JOIN. Отсутствующие поля из левого набора заполняются значениями NULL.
  • Полное внешнее объединение. Всегда содержит как минимум один экземпляр каждой записи каждого объединяемого набора. Отсутствующие поля в записях нового набора заполняются значениями NULL.

Вернемся к таблицам books и editions из базы данных booktown. Если в таблице books содержится общая информация о книгах, то в таблице editions хранятся данные, относящиеся к конкретному изданию — код ISBN, издатель и дата публикации. В таблицу editions входит поле book_id, связывающее ее с полем id, которое является первичным ключом таблицы books.

Допустим, вы хотите получить информацию о каждой книге вместе со всеми имеющимися кодами ISBN. Запрос с внутренним объединением таблиц books и editions вернет набор данных с названиями книг и кодами ISBN, но, как видно из листинга 4.38, если у книги нет печатного издания (или информация об этом издании еще не занесена в базу данных booktown), информация о ней не включается в результат.

Вторая команда в листинге 4.38 использует внешнее объединение и возвращает 20 записей. У трех записей в итоговом наборе отсутствуют коды ISBN, но эти записи все равно включаются в результат.

Листинг 4.38. Внутренние и внешние объединения

booktown=# SELECT title, isbn

booktown-# FROM books INNER JOIN editions

booktown-# ON (books.id = editions.book_id);

title | isbn

The Tell-Tale Heart | 1885418035

The Tell-Tale Heart | 0929605942

Little Women | 0760720002

The Velveteen Rabbit | 0679803335

Goodnight Moon | 0694003611

Bartholomew and the Oobleck | 0394800753

The Cat in the Hat | 039480001X

The Cat in the Hat | 0394900014

Dynamic Anatomy | 0823015505

2001: A Space Odyssey | 0451457994

2001: A Space Odyssey | 0451198492

Dune | 0441172717

Dune | 044100590X

The Shining | 0451160916

The Shining | 0385121679

Franklin in the Dark | 0590445065

Programming Python | 0596000855

(17 rows)

booktown=# SELECT title, isbn

booktown-# FROM books LEFT OUTER JOIN editions

booktown-# ON (books.id = editions.book_id);

title | bisbn

The Tell-Tale Heart | 1885418035

The Tell-Tale Heart | 0929605942

Little Women | 0760720002

The Velveteen Rabbit | 0679803335

Goodnight Moon | 0694003611

Bartholomew and the Oobleck | 0394800753

The Cat in the Hat | 039480001X

The Cat in the Hat | 0394900014

Dynamic Anatomy | 0823015505

2001: A Space Odyssey | 0451457994

2001: A Space Odyssey | 0451198492

Dune | 0441172717

Dune | 044100590X

The Shining | 0451160916

The Shining | 0385121679

Franklin in the Dark | 0590445065

Practical PostgreSQL

Programming Python | 0596000855

Learning Python Perl | Cookbook

(20 rows)

Во второй команде, приведенной в листинге 4.38, использовано левое внешнее объединение (LEFT OUTER JOIN). Выбор объясняется тем, что запрос должен вернуть названия книг, для которых существуют (или не существуют) коды ISBN. Поскольку таблица books стоит слева от ключевого слова JOIN, задача решается при помощи левого внешнего объединения. Если бы помимо названий, не имеющих кодов ISBN, нас интересовали коды ISBN, не имеющие названий, следовало бы воспользоваться полным внешним запросом FULL OUTER JOIN.

Различия между внутренними и внешними объединениями, продемонстрированные в листинге 4.38, чрезвычайно важны, поскольку неправильный выбор объединения приводит к получению лишней информации или потере нужных данных.

ПРИМЕЧАНИЕ

 

Ключевое слово OUTER во внешних объединениях PostgreSQL является необязательным. Определения LEFT JOIN, RIGHT JOIN и FULL JOIN подразумевают внешнее объединение.

 

Сложные объединения

Хотя одна секция JOIN объединяет всего два набора данных, на практике объединения не ограничиваются двумя источниками. За набором, созданным посредством объединения, может следовать новая секция JOIN — по аналогии с тем, как перечисляются через запятую источники данных.

Элементы комбинированных объединений рекомендуется заключать в круглые скобки. Явная группировка гарантирует отсутствие неоднозначности (как для PostgreSQL, так и для разработчика) относительно того, какие наборы и в каком порядке объединяются. Пример объединения нескольких источников данных приведен в листинге 4.39.

Листинг 4.39. Объединение нескольких источников данных

booktown=# SELECT a.last_name, p.name AS publisher, e.isbn, s.subject

booktown-# FROM ((((authors AS a INNER JOIN books AS b</userinput>

booktown(# ON (a.id = b.author_id))</userinput>

booktown(# INNER JOIN editions AS e ON (e.book_id = b.id))

booktown(# INNER JOIN publishers AS p ON (p.id = e.publisher_id))

booktown(# INNER JOIN subjects AS s ON (s.id = b.subjected));

1ast_name | publisher | isbn | subject

Hogarth | Watson-Guptill Publications | 0823015505 | Arts

Brown | HarperCollins | 0694003611 | Children’s Books

Geisel | Random House | 0394800753 | Children’s Books

Geisel | Random House | 039480001X | Children’s Books

Geisel | Random House | 0394900014 | Children’s Books

Bourgeois | Kids Can Press | 0590445065 | Children’s Books

Bianco | Penguin | 0679803335 | Classics

Lutz | O’Reilly & Associates | 0596000855 | Computers

Alcott | Henry Holt & Company. Inc. | 0760720002 | Drama

Рое | Mojo Press | 1885418035 | Horror

Рое | Books of Wonder | 0929605942 | Horror

King | Doubleday | 0451160916 | Horror

King | Doubleday | 0385121679 | Horror

Clarke | Roc | 0451457994 | Science Fiction

Clarke | Roc | 0451198492 | Science Fiction

Herbert | Ace Books | 0441172717 | Science Fiction

Herbert | Ace Books | 044100590X | Science Fiction

(17 rows)

В листинге 4.39 можно заметить одно любопытное обстоятельство: хотя таблица books участвует в объединении, ни одно из ее полей не входит в итоговый набор. Включение таблицы books в секции JOIN предоставляет критерии для объединения других таблиц. Все таблицы, поля которых возвращаются в результате запроса, связываются с другими таблицами через поле id таблицы books (кроме таблицы publishers, которая связывается с таблицей editions по полю publisher_id).