Объединения между таблицами

примечание

Эта страница переведена при помощи нейросети GigaChat.

До сих пор запросы обращались только к одной таблице за раз. Запросы могут обращаться к нескольким таблицам одновременно или к одной и той же таблице таким образом, что одновременно обрабатываются несколько строк таблицы. Запросы, которые обращаются к нескольким таблицам (или нескольким экземплярам одной таблицы) одновременно, называются объединенными запросами. Они объединяют строки из одной таблицы со строками из второй таблицы, причем в выражении указывается, какие строки должны быть объединены в пару. Например, чтобы вернуть все записи о погоде с указанием местоположения связанного с ней города, базе данных нужно сравнить столбец "город" каждой строки таблицы погоды со столбцом "название" всех строк таблицы городов и выбрать те пары строк, в которых эти значения совпадают. Это можно сделать с помощью следующего запроса:

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)

Примечание:

Это лишь концептуальная модель. Обычно объединение выполняется более эффективным способом, чем реальное сравнение каждой возможной пары строк, но это незаметно для пользователя.

Обратите внимание на два момента в наборе результатов:

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

В таком виде могут быть написаны и стыковочные запросы того типа, который рассматривали до сих пор:

SELECT *
    FROM weather, cities
    WHERE city = name;

Этот синтаксис предшествует синтаксису JOIN/ON, который был введен в SQL-92. Таблицы просто перечисляются в предложении FROM, а выражение сравнения добавляется в предложении WHERE. Результаты, полученные с помощью этого старого неявного синтаксиса и более нового явного синтаксиса JOIN/ON, идентичны. Но для читателя запроса явный синтаксис облегчает понимание его смысла – условие объединения вводится отдельным ключевым словом, в то время как раньше оно вставлялось в предложение WHERE вместе с другими условиями.

Теперь выясним, как вернуть записи о Хейворде. Требуется чтобы запрос сканировал weather table и для каждой строки находил соответствующую строку (строки) cities. Если ни одна строка не найдена, то хотим, чтобы в столбцы cities table были подставлены «пустые значения». Такой запрос называется внешним соединением. До сих пор рассматривались внутренние соединения. Команда выглядит следующим образом:

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)

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

Упражнение: Существуют также правые внешние объединения и полные внешние объединения. Попробуйте выяснить, что они делают.

Также можем объединить таблицу с самой собой. Это называется самоприсоединением. В качестве примера предположим, что требуется найти все погодные записи, которые находятся в диапазоне температур других погодных записей. Поэтому нужно сравнить столбцы temp_lo и temp_hi каждой weather rows со столбцами temp_lo и temp_hi всех других weather rows. Можно сделать это с помощью следующего запроса:

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;

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