3.7 Об'єднання таблиць (Join)

Ми бачимо, що наші запити використовують в тільки одну таблицю. Але запити можуть одночасно звертатися до декількох таблиць або до в один і той же час до тієї ж таблиці але з іншим способом обробки довільних записів. Запит, який звертається до кількох записів однієї таблиці або до декількох таблиць одночасно, називається join запитом (об'єднанням). Як приклад, скажімо ви хочете подивитися всі записи про погоду разом з тими містами, до яких вони належать. Щоб це зробити, нам потрібно порівняти поле city кожного запису про погоду в таблиці погоди weather з ім'ям поля всіх записів в таблиці міст cities, і вибрати пари записів, де ці значення збігаються.

Примітка: Це тільки концептуальна модель. Фактично, об'єднання може бути виконано більш ефективно, але це буде не видно користувачеві.

Таким чином, потрібна нам дію буде виконано наступним запитом:

SELECT * FROM weather, cities WHERE 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)

Після виконання якого ми отримаємо два рядки:

• Тут немає запису для міста Hayward, тому що для цього міста немає відповідного запису в таблиці cities, і таким чином, об'єднання ігнорує незбіжні записи в таблиці weather. 

• В отриманому результаті є два поля, що містять ім'я міста. Це правильно, тому що відбувається злиття списку полів таблиць weather і cities. На практиці це небажано, і тому треба явно вказати ті поля, які потрібно, замість використання :

SELECT city, temp_lo, temp_hi, prcp, date, location

FROM weather, citiesWHERE city = name;

Оскільки всі поля мають різні імена, аналізатор автоматично знаходить яке ім'я відповідає якій таблиці, але хорошим стилем у запитах об'єднання є вказівка ​​повного імені поля:

SELECT weather.city, weather.temp_lo, weather.temp_hi,

weather.prcp, weather.date, cities.location

FROM weather, cities

WHERE cities.name = weather.city;

Запити об'єднання такого виду, можуть бути також записані в альтернативній формі, яку значно доцільніше використовувати, ніж попередню:

SELECT * FROM weather INNER JOIN cities ON

(weather.city = cities.name);

Тепер розглянемо як можна отримати записи для міста Hayward. Для цього потрібен такий запит, який би виконав сканування таблиці weather і для кожного запису в ній, знайшов відповідний запис у таблиці cities. Якщо відповідний запис не знайдено, ми хочемо підставити деякі "порожні значення" на місце полів таблиці cities. Такий вид запитів називається outer 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)

Даний запит називається left outer join, тому що для таблиці згадується в лівій частині оператора об'єднання join в отриманому результаті буде видано всі записи принаймні один раз, у той час як для таблиці праворуч в результат потраплять тільки ті записи, які відповідають записам у таблиці ліворуч. Коли буде проводиться висновок записи з таблиці ліворуч, для якої немає відповідного запису в таблиці праворуч, в поля, відповідні таблиці праворуч будуть підставлені порожні (null) значення.

Ми також можемо об'єднувати таблицю з собою ж. Такий вид запитів називається 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, weather W2

WHERE 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

У цьому запиті переназвано таблицю weather іменами W1 і W2 щоб розрізняти ліву і праву частини оператора об'єднання join. Також можна використовувати такі види псевдонімів в інших запитах, щоб зробити запис запиту коротшим, тобто:

SELECT * FROM weather w, cities c WHERE w.city = c.name;

Такий стиль абревіатур зустрічається часто.