SQL в PostgreSQL: объединение таблиц (JOIN)
До сих пор наши запросы обращались только к одной таблице за раз. Запросы могут обращаться к нескольким таблицам одновременно или к одной и той же таблице таким образом, что одновременно обрабатываются несколько строк таблицы. Запросы, которые обращаются к нескольким таблицам (или нескольким экземплярам одной и той же таблицы) одновременно, называются join запросами. Они объединяют строки из одной таблицы со строками из второй таблицы с выражением, определяющим, какие строки должны быть объединены в пары. Например, чтобы вернуть все записи weather вместе с местоположением связанного города, базе данных необходимо сравнить столбец city в каждой строке таблицы weather со столбцом name всех строк в таблице cities и выбрать пары строки, в которых совпадают эти значения. Это можно сделать с помощью следующего запроса:
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)
Это только концептуальная модель. Join обычно выполняется более эффективно, чем сравнение каждой возможной пары строк, но это невидимо для пользователя.
Обратите внимание на две вещи о наборе результатов:
- Строка результатов для города Hayward отсутствует. Это связано с тем, что в таблице городов для Hayward нет соответствующей записи, поэтому объединение игнорирует несогласованные строки в таблице 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;
Широко считается хорошим стилем уточнять все имена столбцов в join запросе, чтобы запрос не завершился ошибкой, если в одну из таблиц позже будет добавлено повторяющееся имя столбца.
Join запросы, которые мы видели до сих пор, также можно записать в этой форме:
SELECT *
FROM weather, cities
WHERE city = name;
Этот синтаксис предшествует синтаксису JOIN/ON, который был введен в SQL-92. Таблицы просто перечислены в предложении FROM, а выражение сравнения добавлено в предложение WHERE. Результаты этого старого неявного синтаксиса и нового явного синтаксиса JOIN/ON идентичны. Но для читателя запроса явный синтаксис облегчает понимание его значения: join условие вводится с помощью его собственного ключевого слова, тогда как ранее условие было смешано в предложении WHERE вместе с другими условиями.
Теперь мы выясним, как мы можем вернуть записи Hayward. Мы хотим, чтобы запрос выполнял сканирование таблицы weather и для каждой строки поиск соответствующих строк с городами. Если соответствующая строка не найдена, мы хотим, чтобы некоторые "пустые значения" были заменены на столбцы таблицы cities. Такой запрос называется outer join (внешним соединением). (Соединения (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, потому что таблица, упомянутая слева от оператора соединения, будет иметь каждую из своих строк на выходе по крайней мере один раз, тогда как таблица справа будет содержать только те строки, которые соответствуют некоторой строке левой таблицы. При выводе строки левой таблицы, для которой нет соответствия правой таблицы, пустые (нулевые) значения заменяются столбцами правой таблицы.
Мы также можем объединить таблицу с самой собой. Это называется самосоединением (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 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)
Здесь мы изменили метку таблицы weather как w1 и w2, чтобы можно было различать левую и правую стороны соединения. Вы также можете использовать эти типы псевдонимов в других запросах, чтобы сэкономить на вводе, например:
SELECT *
FROM weather w JOIN cities c ON w.city = c.name;
Читайте также:
- SQL в PostgreSQL: концепции и создание таблиц
- SQL в PostgreSQL: заполнение таблицы строками
- SQL в PostgreSQL: запрос к таблице
Комментарии
Отправить комментарий