Dołącz (SQL)

SQL - Dołącz (niem Verbund) tworzy z zapisów dwóch tabel o relacyjnej bazie danych tabelę wynikową, której zapisy zawierają atrybuty obu tabel w oparciu o określoną dołączyć stanie. Jest to implementacja koncepcji federacji algebry relacyjnej w języku zapytań SQL. ISO standardem SQL opisuje następujące rodzaje łączy:

• Produkt kartezjański CROSS JOIN,
• wiązanie wewnętrzne w postaci wiązania naturalnego NATURAL JOINi inne warianty, a także
• związek zewnętrzny w postaciach LEFT OUTER JOIN, RIGHT OUTER JOINi FULL OUTER JOIN.

W szczególnym przypadku tabela może być również łączona ze sobą, nazywa się to łączeniem własnym .

Przykładowe tabele

Tabele Mitarbeiteri Abteilungsłużą jako przykłady w celu zademonstrowania różnych form wzajemnych połączeń . Odnotowuje się MIdimię i nazwisko pracownika oraz AbtIdnazwę działu, w którym jest on zatrudniony. Dział posiada atrybuty, AbtIdktóre identyfikują dział i jego nazwę. Sieć łączy informacje z dwóch tabel.

Przykładowe tabele mają następujące cechy szczególne:

• Pracownik o nazwisku „Meyer” nie jest przypisany do żadnego działu. Wartość NULLjak AbtIdw SQL oznacza, że ​​ta wartość jest nieznana .
• Dział „Marketingu” nie ma przypisanych pracowników.

Te dwie tabele można wykorzystać do zilustrowania różnych form asocjacji.

Iloczyn kartezjański (CROSS JOIN)

Te CROSS JOINdwie tabele stanowią iloczyn kartezjański ewidencji danych w obu tabelach. Każdy rekord danych w pierwszej tabeli jest połączony z każdym innym rekordem w drugiej tabeli. Jeśli dwie tabele mają atrybuty o tej samej nazwie, są one uzupełniane przez umieszczenie nazwy tabeli z przodu.

Przykład CROSS JOINs:

SELECT *
FROM Mitarbeiter CROSS JOIN Abteilung

Jawne nazewnictwo produktu kartezjańskiego zostało wprowadzone w standardzie SQL SQL-92 . W standardzie SQL z 1989 roku ten sam wynik można uzyskać za pomocą następującej instrukcji:

SELECT *
FROM Mitarbeiter, Abteilung

Wynik ma 4 × 3 rekordy, ponieważ jest 4 pracowników i 3 działy; składa się ze wszystkich możliwych kombinacji rekordów danych w dwóch tabelach. Ponieważ AbtIdwystępuje to w obu tabelach, dwa atrybuty w tabeli wynikowej są unikatowe przez umieszczenie nazwy tabeli z przodu.

Wewnętrzna więź

Wynikiem sprzężenia wewnętrznego dwóch tabel jest kombinacja rekordów danych danych tabel, które spełniają warunek sprzężenia. Z reguły warunek złączenia wymaga równości wartości pewnych atrybutów w obu tabelach. Może jednak zawierać również inne operatory porównania niż równość.

NATURALNE POŁĄCZENIE

Podstawową formą wiązania wewnętrznego jest wiązanie naturalne NATURAL JOIN.

W przypadku naturalnego dołączyć, że rekordy danych z dwóch tabel zaangażowane są połączone ze sobą, których wartości dopasować atrybuty tej samej nazwie . Tworzone są więc tylko „ odpowiednie ” kombinacje, a nie wszystkie możliwe kombinacje, jak w przypadku produktu kartezjańskiego .

Przykład dla NATURAL JOIN:

SELECT *
FROM Mitarbeiter NATURAL JOIN Abteilung

W naszym przykładzie obie tabele mają wspólny atrybut AbtID. W sieci naturalnej każdemu pracownikowi przypisany jest dział, którego dział jest AbtIdu niego zarejestrowany . Ponieważ wartość AbtIdjest identyczna w obu tabelach w każdym wierszu , atrybut pojawia się w tabeli wyników tylko raz:

Ponieważ pracownik o nazwisku „Meyer” nie jest przypisany do żadnego działu, „M4” nie pojawia się w wyniku. Nie pojawia się również dział „Marketing”, ponieważ nie jest do niego przypisany żaden pracownik.

DOŁĄCZ ... UŻYWAJĄC ...

Przy łączeniu naturalnym kombinacje tworzy się poprzez porównanie wartości wszystkich atrybutów o tej samej nazwie w obu tabelach. Często jednak chcesz wyraźnie określić, który z atrybutów powinien zostać użyty do połączenia zestawów danych. Jest na to formaJOIN ... USING ...

W naszym przykładzie:

SELECT *
FROM Mitarbeiter JOIN Abteilung USING (AbtID)

W tym przykładzie wynik jest identyczny z naturalnym wiązaniem.

Ta forma asocjacji jest NATURAL JOINlepsza niż wariant, ponieważ wyraźna specyfikacja atrybutów zapewnia, że ​​podczas rozwijania odpowiednich tabel nie powstają niepożądane kombinacje. W naszym przykładzie, jeśli do tabeli zostanie wprowadzony Mitarbeiternowy atrybut Ortmiejsca zamieszkania pracownika i do tabeli zostanie Abteilungdodany również atrybut nazwany Ortdla lokalizacji działu, to porównywane są NATURAL JOINatrybuty AbtId i Ort tworzenie powiązanych rekordów danych, co nie odpowiada zamiarowi przydzielenia pracowników do ich działów.

DOŁĄCZ ... NA ...

Możliwe jest również utworzenie sprzężenia między tabelami, w których nazwy porównywanych atrybutów nie są zgodne lub w którym =ma być użyty inny operator niż .

W przykładowych tabelach instrukcja z tą formą złączenia to

SELECT *
FROM Mitarbeiter JOIN Abteilung ON Mitarbeiter.AbtId = Abteilung.AbtId;

Wynik:

Możesz umieścić przed tymi dwoma formami, JOIN ... USING ...a JOIN ... ON ...także opcjonalne słowo kluczowe, INNERaby podkreślić, że jest to związek wewnętrzny.

W standardzie SQL z 1989 roku powyższe stwierdzenie sformułowano by następująco:

SELECT *
FROM Mitarbeiter, Abteilung
WHERE Mitarbeiter.AbtId = Abteilung.AbtId

W pierwszej postaci wyrażenia związek jest określony wyraźnie , podczas gdy w drugim wariancie najpierw tworzy się iloczyn kartezjański, a związek uzyskuje się poprzez kolejne ograniczenie z warunkiem Mitarbeiter.AbtId = Abteilung.AbtId.

Formy unii wewnętrznej w poprzednich przykładach są również określane w literaturze jako equijoin (niem. Gleichverbund ), ponieważ operator równości jest używany w warunku dla unii. W wariancie możliwe jest JOIN ... ON ...również określenie dowolnego warunku powiązania rekordów danych zaangażowanych tabel, np. B. z operatorem porównania ≤. W takim przypadku sprzężenie nazywa się sprzężeniem theta .

Złączenie zewnętrzne (ZŁĄCZENIE ZEWNĘTRZNE)

Pracownik z MId„M4” nie występuje w żadnej z form wewnętrznego grupowania przykładowych tabel , ponieważ nie jest do niego przypisany żaden dział. A dział „Marketingu” nie pojawia się, bo nie ma w nim pracowników.

Formy sprzężenia zewnętrznego zawierają rekordy danych w sprzężeniu, dla których nie ma odpowiednika wartości w dwóch tabelach. Dlatego też sieć zewnętrzna musi być zawsze używana, gdy w grę wchodzą nieznane lub brakujące informacje.

Jeżeli, na przykładzie pracowników i działów, mają zostać wyprowadzeni wszyscy pracownicy i ich działy, także ci, którzy nie są przypisani do żadnego działu, to wymagana jest sieć zewnętrzna.

LEWE POŁĄCZENIE ZEWNĘTRZNE

Wynik z tabel T ₁ i T ₂ zawiera wszystkie rekordy danych w tabeli T ₁ na lewo od słowa kluczowego , nawet jeśli nie ma odpowiedniego rekordu danych w tabeli T _{2 po} prawej stronie . Brakujące wartości z T ₂ są wypełniane NULL. Słowo kluczowe nie jest wymagane w żadnym z przykładów, ale można je napisać, aby zwrócić uwagę czytelnika na zewnętrzny związek. T1 LEFT OUTER JOIN T2JOINOUTER

Na przykładzie pracowników i działów

SELECT *
FROM Mitarbeiter LEFT OUTER JOIN Abteilung USING (AbtId)

Wynik zawiera teraz również pracownika z MId„M4”, a atrybuty z działu tabeli połączonej mają wartość NULL.

PRAWE ZŁĄCZE ZEWNĘTRZNE

Jeden RIGHT OUTER JOIN tworzy wewnętrzne połączenie między dwiema tabelami i uzupełnia je jednym rekordem danych dla rekordów danych w tabeli po prawej stronie, dla których nie ma odpowiednika w tabeli po lewej stronie.

Na przykładzie pracowników i działów

SELECT *
FROM Mitarbeiter RIGHT OUTER JOIN Abteilung USING (AbtId)

Wynik zawiera teraz rekord danych dla działu „Marketing”, do którego nie jest przypisany żaden pracownik, dlatego atrybuty MIdi mają Namewartość NULL.

Inny przykład, w którym wymagana jest sieć zewnętrzna: Wszystkie działy mają być wyprowadzane z liczbą swoich pracowników. Ponieważ AbtIdżaden rekord danych nie zostałby wyprowadzony z linkiem wewnętrznym dla działu z 33, oświadczenie musi być sformułowane z linkiem zewnętrznym:

SELECT AbtName, count(MId) as Mitarbeiterzahl
FROM Mitarbeiter RIGHT OUTER JOIN Abteilung USING (AbtId)
GROUP BY AbtId, AbtName

Wynik:

PEŁNE ZŁĄCZE ZEWNĘTRZNE

Jednym z nich FULL OUTER JOINjest połączenie wyników LEFTi RIGHT OUTER JOINs.

Przykład dla pracowników i działów:

SELECT *
FROM Mitarbeiter FULL OUTER JOIN Abteilung USING (AbtId)

Dalsze warianty składni związku zewnętrznego są zawarte na poniższym schemacie składni.

Dołącz do siebie

Własny przyłączyć to złączenie tabeli z siebie. Oznacza to, że zapisy w tabeli muszą być porównywane z innymi rekordy w tej samej tabeli. Aby móc porównać wartości rekordów danych tej samej tabeli w SQL , musisz przypisać wyraźne nazwy dwóm zmiennym krotki w instrukcji, z których obie mogą przechodzić przez rekordy danych tabeli.

Dla przykładu należy sprawdzić, czy w tabeli Pracownicy znajdują się dwaj pracownicy o tym samym nazwisku, ale o różnych MId. W poniższym self join , zmienne krotki MAi są zdefiniowane MBdla tabeli "Pracownicy" w celu umożliwienia przeprowadzenia porównania.

SELECT MA.MId, MA.Name
FROM Mitarbeiter MA CROSS JOIN Mitarbeiter MB
WHERE MA.MId <> MB.MId AND MA.Name = MB.Name

Wynik to:

W rzeczywistości SQL tworzy zmienną krotkową dla każdej tabeli podczas przetwarzania instrukcji SELECT, która zwykle ma taką samą nazwę jak sama tabela.Jeśli instrukcja to, na przykład select * from Mitarbeiter where Mitarbeiter.MId = 'M1', używana jest zmienna krotkowa o tej samej nazwie co tabela Mitarbeiteri akceptuje każdy rekord tabeli, tak jak jego zawartość. Warunek filtra MId = 'M1'jest następnie sprawdzany przez uruchomienie zmiennej krotki przez tabelę. Możliwe jest jawne nazwanie zmiennej krotki, na przykład select * from Mitarbeiter M where M.MId = 'M1'. Jest to używane w sprzężeniu własnym, aby mieć dwie zmienne krotek dla jednej i tej samej tabeli, dzięki czemu ich rekordy danych mogą być ze sobą porównywane.

Diagram składni

Poniższy diagram składni przedstawia wszystkie formy sprzężenia w SQL.

Kombinacja dwóch „odniesień do tabeli” jest pokazana na diagramie składni. „Odwołanie do tablicy” może samo w sobie być instrukcją dla związku, tj. Oznacza to, że nie tylko połączenia można tworzyć za pomocą dwóch, ale także za pomocą kilku tabel.

W naszym przykładzie wyobraźmy sobie, że istnieje trzecia tabela, Adressektóra używa atrybutu do przypisania AdrIdpracownikowi jego adresu. Następnie poniższe oświadczenie skutkuje przypisaniem adresu i działu do odpowiedniego pracownika:

SELECT *
FROM Mitarbeiter JOIN Adresse using (AdrId)
                 JOIN Abteilung using (AbtId)

Związek wewnętrzny jest (poza kolejnością atrybutów w wyniku) zarówno przemiennym, jak i asocjacyjnym . Sprzężenie zewnętrzne nie jest przemienne i generalnie też nie jest asocjacyjne. Jeśli w zdaniu z kilkoma tabelami używane są różne formy związku, zaleca się stosowanie nawiasów dla jasności.

Specyficzne dla produktu

Różne systemy zarządzania bazami danych częściowo odbiegają od standardu SQL lub mają inne warianty tworzenia sieci zewnętrznej. Poniższe łącza wskazują na dokumentację łączenia niektórych popularnych produktów:

• IBM DB2
  • IBM Db2NATURAL JOIN nie obsługuje tego wariantu .
• Microsoft SQL Server
  • Dialekt SQL dla Microsoft SQL Server nazywa się Transact-SQL .
  • Transact SQL nie obsługuje formularzy NATURAL JOINi JOIN ... USING ...złączeń, więc istnieje tylko wariant, za ONpomocą którego można jednak zarządzać wszystkimi zadaniami.
• MySQL
  • MySQL obsługuje wszystkie formy złączenia zgodne z SQL-92.
  • MySQL ma STRAIGHT JOINspecjalny wariant złączenia. Zazwyczaj optymalizator zapytań systemu zarządzania bazami danych decyduje o tym, jak faktycznie zostanie przeprowadzona federacja. Z STRAIGHT JOINoptymalizatorem MySQL podana jest kolejność, w jakiej powinno zostać wykonane łączenie.
  • MySQL tego FULL [OUTER] JOINnie obsługuje . Ta forma zewnętrznego związku może być utworzona LEFT/RIGHT OUTER JOINwraz z operatorem przez konstrukcję z UNION.
• Wyrocznia
  • Oracle ma również własną składnię dla złączenia zewnętrznego, dopiero w roku 2001 w wersji 9 wprowadzono składnię SQL-92 dla złączenia zewnętrznego.
  • Obecnie Oracle zaleca standardową składnię SQL dla sprzężenia zewnętrznego.
• PostgreSQL
  • PostgreSQL obsługuje wszystkie formy złączenia zgodne z SQL-92. (Dokumentacja PostgreSQL zawiera samouczek dotyczący dołączania .)
• SQLite
  • SQLite obsługuje tylko LEFT OUTER JOIN. Dwie pozostałe formy zewnętrznego związku można wygenerować za LEFT OUTER JOINpomocą operatora razem z operatorem UNION.

Zobacz też

• Dołącz do algebry relacyjnej
• Relacja (baza danych)
• Relacja (matematyka)

literatura

• CJ Date z Hugh Darwenem: Przewodnik po standardzie SQL . Wydanie IV. Addison-Wesley, 1997. 
• Wolfgang Panny z Alfredem Taudesem: Wprowadzenie do podstawowego języka SQL-99 . Springera, 2000. 
• Gunter Saake, Kai-Uwe Sattler i Andreas Heuer: Bazy danych: pojęcia i języki . Wydanie IV. mitp, 2010, s. 221 f. i 339 f . 
• Alfons Kemper i André Eckler: Systemy baz danych: wprowadzenie . Wydanie szóste. Oldenbourg, 2006, s. 125 f . 

Uwagi

1. ↑ Składnia SQL92 wykorzystuje słowa kluczowe do podkreślenia CROSS JOINzamiaru utworzenia iloczynu kartezjańskiego.
2. ↑ W Internecie jest wiele artykułów na temat sieci, w których jest ona zilustrowana diagramami Venna dla operacji na zbiorach . W tych artykułach sprzężenie wewnętrzne jest często tworzone z dwóch tabel o tym samym schemacie i tylko w tym szczególnym przypadku sprzężenie naturalne jest faktycznie identyczne z przecięciem dwóch tabel. Generalnie jednak tak nie jest . W naszym przykładzie możesz podziwiać przecięcie dwóch tabel, Mitarbeitera Abteilungnie formę, ponieważ rekordy dwóch tabel mają różną strukturę i nie są porównywalne. Wizualizacja operatorów złożonych przy użyciu diagramów Venna jest generalnie niedokładna, a zatem myląca.

Indywidualne dowody

1. ↑ CJDate with Hugh Darwen: A Guide to the SQL Standard, Fourth Edition, Addison-Wesley, 1997, s. 135ff
2. ↑ ^{a b} Różne typy związków są opisane w normie SQL SQL: 2011 w części 2 Foundation Section 7.7.
3. ^ Gunther Saake, Kai-Uwe Sattler i Andreas Heuer: Bazy danych: pojęcia i języki . mitp, S. 339 . 
4. ^ Gunther Saake, Kai-Uwe Sattler i Andreas Heuer: Bazy danych: pojęcia i języki . mitp, S. 220 f .