2.6. Объединения таблиц (Join)

Мы видим, что наши запросы используют в только одну таблицу. Но запросы могут одновременно обращаться к нескольким таблицам или к в одно и то же время к той же таблице но с другим способом обработки произвольных записей. Запрос, который обращается к нескольким записям одной таблицы или к нескольким таблицам одновременно, называется join запросом (объединением). В качестве примера, скажем вы хотите посмотреть все записи о погоде вместе с теми городами, к которым они относятся. Чтобы это сделать, нам нужно сравнить поле city каждой записи о погоде в таблице погоды weather с именем поля всех записей в таблице городов cities, и выбрать пары записей, где эти значения совпадают.

Таким образом, нужное нам действие будет выполнено следующим запросом:

SELECT *
    FROM weather, cities
    WHERE city = name;

     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
(2 rows)

После выполнения которого мы получим две строки:

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

• В получившемся результате есть два поля, содержащие имя города. Это правильно, потому что происходит слияние списка полей таблиц weather и cities. На практике это нежелательно, и вы наверное захотите явно указать те поля, которые нужно, вместо использования *:

  SELECT city, temp_lo, temp_hi, prcp, date, location
      FROM weather, cities
      WHERE city = name;

Поскольку все поля имеют разные имена, анализатор автоматически находит какое имя соответствует какой таблице. Если в запросе к двум таблицам были одинаковые имена колонок, вы должны указать полные имена колонок:

SELECT weather.city, weather.temp_lo, weather.temp_hi,
       weather.prcp, weather.date, cities.location
    FROM weather, cities
    WHERE cities.name = weather.city;

Широко распространённым хорошим стилем, является указание полного имени колонок в запросах объединений, а если позже в запрос будут добавлены дублирующие имена колонок, то это не приведёт к ошибке при его выполнении.

Запросы объединения такого вида, могут быть также записаны в альтернативной форме:

SELECT *
    FROM weather INNER JOIN cities ON (weather.city = cities.name);

Этот синтаксис не является общеиспользуемым, но мы показываем этот пример, чтобы помочь вам понять последующий материал.

Теперь мы рассмотрим как можно получить записи для города Hayward, о которых мы говорили ранее. Нам нужен такой запрос, который бы выполнил сканирование таблицы weather и для каждой записи в ней, нашел соответствующую запись в таблице cities. Если соответствующая запись не найдена, мы хотим подставить некоторые "пустые значения" на место полей таблицы cities. Такой вид запросов называется outer join (внешнее объединение). (Которое как мы видим отличается от inner join (внутреннего объединения)). Вот как будет выглядеть запрос:

SELECT *
    FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);

     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
 Hayward       |      37 |      54 |      | 1994-11-29 |               |
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
(3 rows)

Данный запрос называется left outer join, потому что для таблицы упоминаемой в левой части оператора объединения join в полученном результате будут выданы все записи по крайней мере один раз, в то время как для таблицы справа в результат попадут только те записи, которые соответствуют записям в таблице слева. Когда будет производится вывод записи из таблицы слева, для которой нет соответствующей записи в таблице справа, в поля, соответствующие таблице справа будут подставлены пустые (null) значения.

Мы также можем объединять таблицу с собой же. Такой вид запросов называется self join. Предположим, что мы хотим найти все записи о погоде, которые находятся в определенном диапазоне температур. Для этого нам необходимо сравнить значения полей temp_lo и temp_hi для каждой записи о погоде weather с значениями полей temp_lo и temp_hi для всех остальных записей о погоде weather. Мы можем сделать это с помощью следующего запроса:

SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
    W2.city, W2.temp_lo AS low, W2.temp_hi AS high
    FROM weather W1, weather W2
    WHERE W1.temp_lo < W2.temp_lo
    AND W1.temp_hi > W2.temp_hi;

     city      | low | high |     city      | low | high
---------------+-----+------+---------------+-----+------
 San Francisco |  43 |   57 | San Francisco |  46 |   50
 Hayward       |  37 |   54 | San Francisco |  46 |   50
(2 rows)

В этом запросе мы переназвали таблицу weather именами W1 и W2 чтобы различать левую и правую части оператора объединения join. Мы можем также использовать такие виды псевдонимов в других запросах, чтобы сделать запись запроса более короткой, т.е.:

SELECT *
    FROM weather w, cities c
    WHERE w.city = c.name;

Вы увидите, что такой стиль аббревиатур встречается часто.