Как было показано в примере использования секции WHERE для выборки из двух таблиц, существует возможность выборки данных из разных источников с объединением их полей. В SQL этот процесс формально называется объединением (join).
В результате объединения двух или более наборов данных создается новый набор записей, состоящих из всех полей исходных наборов. Базовый вариант объединения представляет собой декартово произведение, то есть совокупность всех возможных комбинаций двух наборов. Далее из этого произведения отбирается часть записей по критериям, заданным в секции JOIN.
Существуют три разновидности объединений.
CROSS JOIN). Декартово (перекрестное) произведение двух наборов данных. Произведение не определяет отношений между наборами, а лишь содержит все возможные комбинации записей объединяемых наборов.
INNER JOIN). Подмножество декартова произведения двух наборов данных с критерием, используемым для объединения записей. Критерий возвращает логическую величину – признак вхождения записи в объединенный набор.
OUTER JOIN). Как и внутренние объединения, содержат критерий объединения записей, но обязательно возвращают минимум один экземпляр каждой записи заданного набора. Это может быть левый набор (источник данных слева от ключевого слова JOIN), правый набор (источник данных справа от ключевого слова JOIN) или оба набора в зависимости от конкретной разновидности внешнего объединения. Пустые поля в тех частях записей, которые не отвечают критерию объединения, содержат NULL.
Результат перекрестного объединения принципиально не отличается от перечисления источников через запятую. Следовательно, в команде выборки с перекрестным объединением практически всегда должна присутствовать секция WHERE, уточняющая связи между объединенными наборами данных. В листинге 22 приведен запрос из листинга 15, в котором перечисление источников заменено формальным синтаксисом JOIN.
Листинг 22. Простой перекрестный запрос.
booktown=# SELECT b.id, title, a.id, last_name FROM books AS b CROSS JOIN authors AS a 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 обязательно содержит критерий, уточняющий связи между объединяемыми наборами данных. Синтаксис внутренних и внешних объединений:
источник1 [ NATURAL ] тип_объединения источник2
[ ON (условие [….]) I USING (поле [,…]) ]источник1. Первый из объединяемых наборов данных (имя таблицы или подзапрос).
[ 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, приведенные в листинге 23.
Листинг 23. Сравнение команд INNER JOIN и WHERE.
booktown=# SELECT title, last_name, first_name FROM books, authors WHERE (books.author_id = authors.id) 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 FROM books AS b INNER JOIN authors AS a ON (b.author_id = a.id) 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)Две синтаксические формы в листинге 23 функционально идентичны и возвращают одинаковые результаты. Синтаксис INNER JOIN позволяет отделить критерий связи источников от критерия выбора записей, поскольку связи определяются только в секции ON. Это существенно упрощает чтение и модификацию запросов, поскольку программисту не приходится разбираться в смысле каждого условия в секции WHERE.
Обратите внимание: во втором запросе продемонстрировано назначение синонимов а и b в секции ON для таблиц books и authors соответственно. Подобное использование синонимов в секции ON абсолютно законно, более того – часто оно предпочтительно, поскольку программа становится более наглядной.
В случае простых объединений по совпадающим значениям вместо ON иногда бывает удобнее использовать секции USING и NATURAL. Впрочем, они применимы лишь к наборам данным, содержащим одноименные поля. Если поля, по которым устанавливается связь между наборами, имеют разные имена, все равно остается возможность использования секций USING и NATURAL благодаря назначению синонимов полей (листинг 24).
Листинг 24. Секции NATURAL и USING.
booktown=# SELECT title, last_name, first_name
FROM books INNER JOIN authors AS a (author_id)
USING (author_id)
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
FROM books NATURAL INNER JOIN authors AS a (author_id)
WHERE lastname = 'Geisel';
title | last_name | first_name
The Cat in the Hat | Geisel | Theodor Seuss
Bartholomew and the Oobleck | Geisel | Theodor Seuss
(2 rows)Первая команда SELECT в листинге 24 назначает синоним author_id первому полю таблицы 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, но, как видно из листинга 25, если у книги нет печатного издания (или информация об этом издании еще не занесена в базу данных booktown), информация о ней не включается в результат.
Вторая команда в листинге 25 использует внешнее объединение и возвращает 20 записей. У трех записей в итоговом наборе отсутствуют коды ISBN, но эти записи все равно включаются в результат.
Листинг 25. Внутренние и внешние объединения.
booktown=# SELECT title, isbn FROM books INNER JOIN editions ON (books.id = editions.book_id);
booktown=# SELECT title, isbn FROM books LEFT OUTER JOIN editions ON (books.id = editions.book_id);Во второй команде, приведенной в листинге 25, использовано левое внешнее объединение (LEFT OUTER JOIN). Выбор объясняется тем, что запрос должен вернуть названия книг, для которых существуют (или не существуют) коды ISBN. Поскольку таблица books стоит слева от ключевого слова JOIN, задача решается при помощи левого внешнего объединения. Если бы помимо названий, не имеющих кодов ISBN, нас интересовали коды ISBN, не имеющие названий, следовало бы воспользоваться полным внешним запросом FULL OUTER JOIN.
Различия между внутренними и внешними объединениями, продемонстрированные в листинге 25, чрезвычайно важны, поскольку неправильный выбор объединения приводит к получению лишней информации или потере нужных данных.
Примечание. Ключевое слово OUTER во внешних объединениях PostgreSQL является необязательным. Определения LEFT JOIN, RIGHT JOIN и FULL JOIN подразумевают внешнее объединение.
Хотя одна секция JOIN объединяет всего два набора данных, на практике объединения не ограничиваются двумя источниками. За набором, созданным посредством объединения, может следовать новая секция JOIN – по аналогии с тем, как перечисляются через запятую источники данных.
Элементы комбинированных объединений рекомендуется заключать в круглые скобки. Явная группировка гарантирует отсутствие неоднозначности (как для PostgreSQL, так и для разработчика) относительно того, какие наборы и в каком порядке объединяются. Пример объединения нескольких источников данных приведен в листинге 26.
Листинг 26. Объединение нескольких источников данных.
booktown=# SELECT a.last_name, p.name AS publisher, e.isbn, s.subject
FROM ((((authors AS a INNER JOIN books AS b
ON (a.id = b.author_id))
INNER JOIN editions AS e ON (e.book_id = b.id))
INNER JOIN publishers AS p ON (p.id = e.publisher_id))
INNER JOIN subjects AS s ON (s.id = b.subject_id));
last_name | publisher | isbn | subject
Hogarth | Watson-Guptill| 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 | 0596000855 | Computers
Alcott | Henry Holt | 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)В листинге 26 можно заметить одно любопытное обстоятельство: хотя таблица books участвует в объединении, ни одно из ее полей не входит в итоговый набор. Включение таблицы books в секции JOIN предоставляет критерии для объединения других таблиц. Все таблицы, поля которых возвращаются в результате запроса, связываются с другими таблицами через поле id таблицы books (кроме таблицы publishers, которая связывается с таблицей editions по полю publisher_id).
| Уточнение запросов | Группировка записей |