2.6. Соединения между таблицами#
2.6. Соединения между таблицами
До сих пор наши запросы обращались только к одной таблице за раз.
Запросы могут обращаться к нескольким таблицам одновременно или обращаться к одной и той же таблице таким образом, что обрабатываются несколько строк таблицы одновременно. Запросы, которые обращаются к нескольким таблицам (или нескольким экземплярам одной и той же таблицы) одновременно, называются запросами join. Они объединяют строки из одной таблицы с строками из второй таблицы с помощью выражения, указывающего, какие строки должны быть сопоставлены. Например, чтобы вернуть все записи о погоде вместе с местоположением соответствующего города, базе данных необходимо сравнить столбец city каждой строки таблицы weather со столбцом name всех строк в таблице cities и выбрать пары строк, где эти значения совпадают.
[4]
Это можно сделать с помощью следующего запроса:
SELECT * FROM weather JOIN cities ON 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)
Обратите внимание на две вещи о наборе результатов:
Нет результатов для города Хейвард. Это происходит потому, что в таблице
citiesнет соответствующей записи для Хейварда, поэтому соединение игнорирует несоответствующие строки в таблицеweather. Скоро мы увидим, как это можно исправить.Есть два столбца, содержащих название города. Это правильно, потому что списки столбцов из таблиц
weatherиcitiesобъединяются. Однако на практике это нежелательно, поэтому вы, вероятно, захотите явно перечислить выходные столбцы, а не использовать*:SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather JOIN cities ON city = name;
Поскольку у всех столбцов были разные имена, парсер автоматически определил, к какой таблице они относятся. Если бы в двух таблицах были дублирующиеся имена столбцов, вам нужно было бы уточнить имена столбцов, чтобы показать, на какой из них вы ссылаетесь, например:
SELECT weather.city, weather.temp_lo, weather.temp_hi,
weather.prcp, weather.date, cities.location
FROM weather JOIN cities ON weather.city = cities.name;
Считается хорошим стилем квалифицировать все имена столбцов в запросе с объединением, чтобы запрос не завершился ошибкой, если позже в одной из таблиц будет добавлено дублирующееся имя столбца.
Запросы слияния, подобные тем, что были рассмотрены ранее, также могут быть записаны в следующей форме:
SELECT *
FROM weather, cities
WHERE city = name;
Этот синтаксис предшествует синтаксису JOIN/ON,
который был введен в SQL-92. Таблицы просто перечисляются в
предложении FROM, а выражение сравнения добавляется
в предложение WHERE. Результаты от этого старого
неявного синтаксиса и нового явного
синтаксиса JOIN/ON идентичны. Но
для читателя запроса явный синтаксис делает его значение более понятным: Условие объединения вводится собственным ключевым словом, в то время как
ранее условие было смешано в предложение WHERE
вместе с другими условиями.
Теперь мы разберемся, как мы можем вернуть записи Hayward.
Что мы хотим, чтобы запрос делал, это сканировал таблицу 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 JOIN weather w2
ON 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 JOIN cities c ON w.city = c.name;
Вы часто столкнетесь с таким стилем сокращения.
[4] Это только концептуальная модель. Обычно соединение выполняется более эффективным способом, чем сравнение каждой возможной пары строк, но это невидимо для пользователя.