Как всъщност работят SQL JOINs Блогът на AnatolyКорсаков

Напоследък видях няколко популярни статии в блогове, обясняващи JOIN с помощта на диаграма на Venn. В края на краищата релационната алгебра и SQL са теории и езици за множества, така че има смисъл да се илюстрират операциите с множество като JOIN с помощта на диаграми на Venn. нали

Google потвърждава това:

Всеки използва диаграмите на Вен, за да обясни съединенията. Но това…

ПРОСТО ГРЕШНО!

Диаграмите на Вен са чудесни за илюстриране на операции с множество! SQL познава три от тях:

И те могат да бъдат обяснени така:

(тези слайдове са взети от презентацията на обучението JOOQ)

Повечето от вас рядко използват UNION, INTERSECT и EXCEPT са още по-екзотични.

Изводът: тези операции работят върху набори от елементи (кортежи), които имат един и същи тип. Както можете да видите в примерите по-горе, всички елементи са записи на хора с име и фамилия.Също така INTERSECT и EXCEPT се използват рядко, защото обикновено са безполезни. JOIN е много по-полезен. Например искате да свържете набор от актьори към свързания с него набор от филми.

JOIN всъщност е декартов продукт с филтри. Ето добра илюстрация на декартовия продукт:

Кой е най-добрият начин за илюстриране на JOIN операции?

ПРИСЪЕДИНЕТЕ СЕ към класации! Нека първо да разгледаме CROSS JOIN. защото други типове JOIN могат да бъдат изведени от CROSS JOIN:

Не забравяйте, че кръстосаното свързване се прави по следния начин: взема всеки елемент от лявата страна и се свързва с всеки елемент от дясната страна. Когато правите таблица с 3 записа CROSS JOIN с таблица,който има 4 записа, ще получите 3×4=12 резултатни записа.

ВЪТРЕШНО СЪЕДИНЕНИЕ

Всички други съединения са базирани на кръстосано съединение, но с допълнителни филтри и евентуални съединения. Ето обяснение на всеки тип JOIN.

INNER JOIN е CROSS JOIN, който съдържа само онези комбинации, които са запазени според дадения предикат. Например:

ВЪНШНО СЪЕДИНЕНИЕ

Типовете OUTER JOIN ни помагат там, където искаме да запазим само онези записи, или от ЛЯВАТА страна, или от ДЯСНАТА страна, или и от двете (ПЪЛНИ) страни, които съответстват на предиката.

ЛЯВО ВЪНШНО СЪЕДИНЕНИЕ в релационната алгебра се дефинират като:

Но никой не иска да пише толкова много SQL, поради което бяха въведени OUTER JOIN.

Има следните таблици със стойности на цветовете:

ИЗБЕРЕТЕ A.Color, B.Color ОТ КРЪСТОСТ B

Кръстосано съединение (или декартово произведение) дава резултат с всяка комбинация от записи от две таблици. Всяка таблица има 4 записа и резултатът ще съдържа 16 записа.

ИЗБЕРЕТЕ A.Colour, B.Colour ОТ ВЪТРЕШНО СЪЕДИНЕНИЕ B НА A.Colour = B.Colour

Вътрешното присъединяване ще върне всички записи от кръстосаното присъединяване, които отговарят на условието за присъединяване. В този случай имаме 5 записа:

ИЗБЕРЕТЕ A.Color, B.Color ОТ ВЪТРЕШНО СЪЕДИНЕНИЕ B НА A.Color НЕ ВЪВ („Зелен“, „Син“)

Условието за присъединяване не трябва да е условие за равенство и не винаги може да се отнася до колони от двете (или дори една) от таблиците. Оценката на условието A.Color NOT IN ('Green', 'Blue') на всеки ред от кръстосаното съединение връща:

ИЗБЕРЕТЕ A.Colour, B.Colour ОТ ЛЯВО ВЪНШНО СЪЕДИНЕНИЕ B НА A.Colour = B.Colour

Външните съединения се оценяват логически по същия начин като вътрешните съединения, с изключение на това, че записът е от лявата таблица(или дясно) не се съединява с нито един от редовете от дясната таблица, то ще бъде върнато с NULL стойност за дясната колона.

ИЗБЕРЕТЕ A.Colour, B.Colour ОТ ЛЯВО ВЪНШНО СЪЕДИНЕНИЕ B НА A.Colour = B.Colour, КЪДЕТО B.Colour Е NULL

Този пример просто ограничава резултата от предишния пример, сега резултатът връща записи, където B.Color Е NULL. В този конкретен случай това ще бъдат редовете, които нямат съвпадения от дясната страна и заявката ще върне единствения червен ред, който няма съвпадения в таблица B. Този трик е известен също като антиполусъединяване.

ИЗБЕРЕТЕ A.Colour, B.Colour ОТ ДЯСНО ВЪНШНО СЪЕДИНЕНИЕ B НА A.Colour = B.Colour

RIGHT OUTER JOIN действа по същия начин като LEFT OUTER JOIN, с изключение на това, че съхранява несъвпадащи записи от дясната таблица и NULL като стойност на записа от лявата таблица.

ИЗБЕРЕТЕ A.Colour, B.Colour ОТ ПЪЛНО ВЪНШНО СЪЕДИНЕНИЕ B НА A.Colour = B.Colour

FULL OUTER JOIN комбинира поведението на ляво и дясно съединение и съхранява несъвпадащи записи от лявата и дясната таблица.

Заключение

Кажете НЕ на диаграмата на Вен!

Съединенията са сравнително лесни за интуитивно разбиране. И те са относително лесни за обяснение с помощта на диаграми на Вен. Но винаги, когато правите това, не забравяйте, че правите грешна аналогия. JOIN не е строго дефинирана множествена операция, която може да бъде описана чрез диаграми на Вен. JOIN винаги е декартово произведение с предикат и евентуално UNION за добавяне на записи към резултата от OUTER JOIN.

Така че, когато се съмнявате, използвайте JOIN диаграми, а не диаграми на Вен. Те са по-точни и визуално по-полезни.

---