Як насправді працюють операції JOIN у SQL Блог АнатоліяКорсакова
Останнім часом я зустрічав кілька популярних статей у блогах, які пояснюють JOINs, використовуючи Діаграму Венна. Зрештою, реляційна алгебра і SQL — це теорії та мови, пов'язані з множинами, тому має сенс ілюструвати операції над безліччю як JOINs, використовуючи діаграми Венна. Правильно?
Google підтверджує це:
Усі використовують діаграми Венна для пояснення джойнів. Але це…
ПРОСТО НЕПРАВИЛЬНО!
Діаграми Венна чудово підходять для ілюстрації операцій над безліччю! У SQL відомо три з них:
І вони можуть бути пояснені так:
(ці слайди взяті з презентації тренінгу JOOQ)
Більшість із Вас рідко використовують операцію UNION, INTERSECT та EXCEPT ще екзотичніші.
Суть: операції оперують наборами елементів (кортежами), які мають однаковий тип. Як видно на прикладах зверху, всі елементи це записи про людей з ім'ям та прізвищем. Також INTERSECT і EXCEPT рідко використовуються, тому що зазвичай вони марні. JOIN набагато корисніший. Наприклад, Ви хочете з'єднати набір акторів із пов'язаним із ним набором фільмів.
JOIN насправді декартовий твір із фільтрами. Наведемо гарну ілюстрацію декартового твору:
То який найкращий спосіб ілюструвати операції JOIN?
Діаграми JOINів! Давайте спочатку глянемо на CROSS JOIN. тому що інші типи JOIN можуть бути виведені з CROSS JOIN:
Запам'ятайте, перехресне з'єднання (cross join) відбувається таким чином: береться кожен елемент з лівої сторони, і з'єднується з кожним елементом з правого боку. Коли ви робите CROSS JOIN таблиці з 3 записами з таблицею,в якій 4 записи, то отримаєте 3×4=12 результуючих записів.
INNER JOIN
Всі інші джойни базуються на перехресному джойні, але з додатковими фільтрами та можливо об'єднаннями. Наведемо пояснення кожного типу JOIN.
INNER JOIN це CROSS JOIN, в якому містяться лише ті комбінації, які збереглися відповідно до заданого предикату. Наприклад:
OUTER JOIN
Типи OUTER JOIN допомагають нам, там де ми хочемо залишити тільки ті записи, або з лівої сторони, або з правої або з обох (FULL) сторін, які відповідають предикату.
LEFT OUTER JOIN у реляційній алгебрі визначаються як:
Але ніхто не хоче писати стільки SQL, тому OUTER JOIN було введено.
Є такі таблиці зі значеннями кольорів:
SELECT A.Colour, B.Colour FROM A CROSS JOIN B
Cross join (або декартовий твір) створює результат з кожною комбінацією записів із двох таблиць. Кожна таблиця має по 4 записи та результат міститиме 16 записів.
SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour = B.Colour
Inner join поверне всі записи з перехресного з'єднання, які відповідають умові join. У цьому випадку маємо 5 записів:
SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour NOT IN ('Green','Blue')
Умова join не обов'язково має бути умовою рівності і не завжди може мати посилання на стовпці з обох (або навіть однієї) таблиць. Обчислення умови A.Colour NOT IN ('Green','Blue') на кожному рядку cross join повертає:
SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour
Outer join логічно обчислюються таким же способом, як і inner джойни, крім того, що якщо запис з лівої таблиці(або правою) не з'єднується з будь-яким із рядків з правої таблиці вона буде внесена в результат зі значенням NULL для правої колонки.
SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Color WHERE B.Colour IS NULL
У цьому прикладі просто обмежується результат попереднього прикладу, тепер повертає результати записи, в яких B.Colour IS NULL. У цьому конкретному випадку це будуть рядки, які не мають збігів з правого боку і запит поверне єдиний червоний рядок, який не має збігів із таблицею B. Цей трюк також відомий як anti semi join.
SELECT A.Colour, B.Colour FROM A RIGHT OUTER JOIN B ON A.Colour = B.Colour
RIGHT OUTER JOIN діє так само, як і LEFT OUTER JOIN, крім того, що він зберігає записи, що не співпадають з правої таблиці і NULL як значення запису з лівої таблиці.
SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON A.Colour = B.Colour
FULL OUTER JOIN комбінує поведінку лівого та правого джойна і зберігає записи, що не збігаються, з лівої та правої таблиці.
Висновок
Скажи НІ діаграмі Венна!
Джойни щодо легко зрозуміти інтуїтивно. І їх можна порівняно легко пояснити за допомогою діаграм Венна. Але щоразу, коли Ви робите це, запам'ятайте, що Ви робите неправильну аналогію. JOIN не є строго заданою операцією над множинами, що може бути описана діаграмами Венна. JOIN завжди декартовий твір з предикатом, і можливо UNION для додавання записів до результату OUTER JOIN.
Отже, якщо сумніваєтеся, то застосовуйте діаграми JOINів, краще ніж діаграми Венна. Вони точніші і візуально корисніші.