2.6. Соединения таблиц

До этого все наши запросы обращались только к одной таблице. Однако запросы могут также обращаться сразу к нескольким таблицам или обращаться к той же таблице так, что одновременно будут обрабатываться разные наборы её строк. Запрос, обращающийся к разным наборам строк одной или нескольких таблиц, называется соединением (JOIN). Например, мы захотели перечислить все погодные события вместе с координатами соответствующих городов. Для этого мы должны сравнить колонку city каждой строки таблицы weather с колонкой name всех строк таблицы 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);

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

Сейчас мы выясним, как вернуть записи о погоде в городе Хейуорд. Мы хотим, чтобы запрос просканировал таблицу weather и для каждой её строки нашёл соответствующую строку в таблице cities. Если же такая строка не будет найдена, мы хотим, чтобы вместо значений колонок из таблицы cities были подставлены "пустые значения". Запросы такого типа называются внешними соединениями. (Соединения, которые мы видели до этого, называются внутренними.) Эта команда будет выглядеть так:

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)

Этот запрос называется левым внешним соединением, потому что из таблицы в левой части оператора будут выбраны все строки, а из таблицы справа только те, которые удалось сопоставить каким-нибудь строкам из левой. При выводе строк левой таблицы, для которых не удалось найти соответствия в правой, вместо колонок правой таблицы подставляются пустые значения (NULL).

В соединении мы также можем замкнуть таблицу на себя. Это называется замкнутым соединением. Например, представьте, что мы хотим найти все записи погоды, в которых температура лежит в диапазоне температур других записей. Для этого мы должны сравнить колонки 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, чтобы можно было различить левую и правую стороны соединения. Вы можете использовать подобные псевдонимы и в других запросах для сокращения:

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

Вы будете встречать сокращения такого рода довольно часто.