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

примечание

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

До этого запросы обращались только к одной таблице за раз. SQL позволяет работать с несколькими таблицами одновременно или даже с одной таблицей так, чтобы в обработке участвовали несколько ее строк. Такие запросы называются объединенными. Они сопоставляют строки из одной таблицы со строками другой, при этом в условии указывается, какие строки должны составить пару.

Например, чтобы вернуть все записи о погоде с указанием местоположения города, необходимо сравнить столбец 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)

примечание

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

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

• Для города Хейворд строка отсутствует. Это связано с тем, что в таблице cities нет записи для Хейворда, и объединение игнорирует несопоставленные строки таблицы weather.
• В выборке появились два столбца с названием города. Такое поведение корректно, так как список столбцов формируется объединением списков обеих таблиц. На практике чаще явно перечисляют выходные столбцы, избегая *:

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/ON:

SELECT *
    FROM weather, cities
    WHERE city = name;

Разница лишь в том, что SQL-92 добавил явный синтаксис JOIN/ON. Он облегчает чтение: условие соединения отделяется ключевым словом ON, а не смешивается с другими условиями в WHERE.

Чтобы вернуть также данные о Хейворде, используется внешнее соединение. В этом случае для каждой строки таблицы weather выбираются совпадения из cities. Если совпадений нет, в столбцы второй таблицы подставляются NULL.

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)

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

Упражнение: попробуйте изучить работу правых и полных внешних соединений.

Возможна и ситуация, когда таблица объединяется сама с собой — самоприсоединение. Например, чтобы найти погодные записи, диапазоны температур которых включают диапазоны других записей:

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;

Этот прием часто используется на практике.