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)
Обратите внимание на две особенности полученных данных:
В результате нет строки с городом Хейуорд (Hayward). Так получилось потому, что в таблице
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 наряду с другими условиями.
Сейчас мы выясним, как вернуть записи о погоде в городе Хейуорд. Мы хотим, чтобы запрос просканировал таблицу 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)
Здесь мы ввели новые обозначения таблицы weather: W1 и W2, чтобы можно было различить левую и правую стороны соединения. Вы можете использовать подобные псевдонимы и в других запросах для сокращения:
SELECT *
FROM weather w JOIN cities c ON w.city = c.name;Вы будете встречать сокращения такого рода довольно часто.
[4] Это не совсем точная модель. Обычно соединения выполняются эффективнее (сравниваются не все возможные пары строк), но это скрыто от пользователя.
2.6. Joins Between Tables #
Thus far, our queries have only accessed one table at a time. Queries can access multiple tables at once, or access the same table in such a way that multiple rows of the table are being processed at the same time. Queries that access multiple tables (or multiple instances of the same table) at one time are called join queries. They combine rows from one table with rows from a second table, with an expression specifying which rows are to be paired. For example, to return all the weather records together with the location of the associated city, the database needs to compare the city column of each row of the weather table with the name column of all rows in the cities table, and select the pairs of rows where these values match.[4] This would be accomplished by the following query:
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)
Observe two things about the result set:
There is no result row for the city of Hayward. This is because there is no matching entry in the
citiestable for Hayward, so the join ignores the unmatched rows in theweathertable. We will see shortly how this can be fixed.There are two columns containing the city name. This is correct because the lists of columns from the
weatherandcitiestables are concatenated. In practice this is undesirable, though, so you will probably want to list the output columns explicitly rather than using*:SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather JOIN cities ON city = name;
Since the columns all had different names, the parser automatically found which table they belong to. If there were duplicate column names in the two tables you'd need to qualify the column names to show which one you meant, as in:
SELECT weather.city, weather.temp_lo, weather.temp_hi,
weather.prcp, weather.date, cities.location
FROM weather JOIN cities ON weather.city = cities.name;
It is widely considered good style to qualify all column names in a join query, so that the query won't fail if a duplicate column name is later added to one of the tables.
Join queries of the kind seen thus far can also be written in this form:
SELECT *
FROM weather, cities
WHERE city = name;
This syntax pre-dates the JOIN/ON syntax, which was introduced in SQL-92. The tables are simply listed in the FROM clause, and the comparison expression is added to the WHERE clause. The results from this older implicit syntax and the newer explicit JOIN/ON syntax are identical. But for a reader of the query, the explicit syntax makes its meaning easier to understand: The join condition is introduced by its own key word whereas previously the condition was mixed into the WHERE clause together with other conditions.
Now we will figure out how we can get the Hayward records back in. What we want the query to do is to scan the weather table and for each row to find the matching cities row(s). If no matching row is found we want some “empty values” to be substituted for the cities table's columns. This kind of query is called an outer join. (The joins we have seen so far are inner joins.) The command looks like this:
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)
This query is called a left outer join because the table mentioned on the left of the join operator will have each of its rows in the output at least once, whereas the table on the right will only have those rows output that match some row of the left table. When outputting a left-table row for which there is no right-table match, empty (null) values are substituted for the right-table columns.
Exercise: There are also right outer joins and full outer joins. Try to find out what those do.
We can also join a table against itself. This is called a self join. As an example, suppose we wish to find all the weather records that are in the temperature range of other weather records. So we need to compare the temp_lo and temp_hi columns of each weather row to the temp_lo and temp_hi columns of all other weather rows. We can do this with the following query:
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)
Here we have relabeled the weather table as w1 and w2 to be able to distinguish the left and right side of the join. You can also use these kinds of aliases in other queries to save some typing, e.g.:
SELECT *
FROM weather w JOIN cities c ON w.city = c.name;
You will encounter this style of abbreviating quite frequently.
[4] This is only a conceptual model. The join is usually performed in a more efficient manner than actually comparing each possible pair of rows, but this is invisible to the user.