Объединение наборов данных Как было показано в примере использования секции 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).