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

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

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

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

Результат:

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

(2 rows)

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

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)

Этот запрос называется левым внешним соединением, поскольку в результирующий набор попадут все строки таблицы, указанной слева от оператора JOIN, и только те строки таблицы указанной справа, которые соответствуют условию указанному в предложении ON. Для записей левой таблицы, которые не соответствуют условию, значение столбца из правой таблицы будет пустыми (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)

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

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

Этот стиль сокращения встречается довольно часто.