Ogólny pomysł
Proszę zobaczyć odpowiedź przez Martina Smitha przez lepszych illustations i objaśnienie różnych łączy, a zwłaszcza w tym różnice między FULL OUTER JOIN, RIGHT OUTER JOINi LEFT OUTER JOIN.
Te dwie tabele stanowią podstawę do przedstawienia JOINponiższych s:
KRZYŻ DOŁĄCZ
SELECT *
FROM citizen
CROSS JOIN postalcode
Rezultatem będą produkty kartezjańskie wszystkich kombinacji. Nie JOINwymaga warunek:
DOŁĄCZ DO WEWNĘTRZNEGO
INNER JOIN jest taki sam jak po prostu: JOIN
SELECT *
FROM citizen c
JOIN postalcode p ON c.postal = p.postal
Rezultatem będą kombinacje, które spełniają wymagany JOINwarunek:
DOŁĄCZ DO LEWEGO ZEWNĘTRZNEGO
LEFT OUTER JOIN jest taki sam jak LEFT JOIN
SELECT *
FROM citizen c
LEFT JOIN postalcode p ON c.postal = p.postal
Rezultatem będzie wszystko, citizennawet jeśli nie będzie żadnych dopasowań postalcode. Ponownie JOINwymagany jest warunek:
Dane do gry
Wszystkie przykłady zostały uruchomione na Oracle 18c. Są one dostępne na stronie dbfiddle.uk, skąd pochodzą również zrzuty ekranów tabel.
CREATE TABLE citizen (id NUMBER,
name VARCHAR2(20),
postal NUMBER, -- <-- could do with a redesign to postalcode.id instead.
leader NUMBER);
CREATE TABLE postalcode (id NUMBER,
postal NUMBER,
city VARCHAR2(20),
area VARCHAR2(20));
INSERT INTO citizen (id, name, postal, leader)
SELECT 1, 'Smith', 2200, null FROM DUAL
UNION SELECT 2, 'Green', 31006, 1 FROM DUAL
UNION SELECT 3, 'Jensen', 623, 1 FROM DUAL;
INSERT INTO postalcode (id, postal, city, area)
SELECT 1, 2200, 'BigCity', 'Geancy' FROM DUAL
UNION SELECT 2, 31006, 'SmallTown', 'Snizkim' FROM DUAL
UNION SELECT 3, 31006, 'Settlement', 'Moon' FROM DUAL -- <-- Uuh-uhh.
UNION SELECT 4, 78567390, 'LookoutTowerX89', 'Space' FROM DUAL;
Rozmyte granice podczas gry z JOINiWHERE
KRZYŻ DOŁĄCZ
CROSS JOINw wyniku czego powstały wiersze jako Ogólna idea / INNER JOIN:
SELECT *
FROM citizen c
CROSS JOIN postalcode p
WHERE c.postal = p.postal -- < -- The WHERE condition is limiting the resulting rows
Użycie CROSS JOINdo uzyskania wyniku LEFT OUTER JOINwymaga sztuczek, takich jak dodawanie z NULLrzędu. Zostało pominięte
DOŁĄCZ DO WEWNĘTRZNEGO
INNER JOINstaje się produktami kartezjańskimi. To to samo, co Ogólny pomysł / CROSS JOIN:
SELECT *
FROM citizen c
JOIN postalcode p ON 1 = 1 -- < -- The ON condition makes it a CROSS JOIN
W tym miejscu połączenie wewnętrzne można naprawdę postrzegać jako połączenie krzyżowe z usuniętymi wynikami niezgodnymi z warunkiem. Tutaj żaden z wynikowych wierszy nie jest usuwany.
Użycie INNER JOINdo uzyskania wyniku LEFT OUTER JOINrównież wymaga sztuczek. Zostało pominięte
DOŁĄCZ DO LEWEGO ZEWNĘTRZNEGO
LEFT JOINwyniki w wierszach jako Ogólna idea / CROSS JOIN:
SELECT *
FROM citizen c
LEFT JOIN postalcode p ON 1 = 1 -- < -- The ON condition makes it a CROSS JOIN
LEFT JOINwyniki w wierszach jako Ogólna idea / INNER JOIN:
SELECT *
FROM citizen c
LEFT JOIN postalcode p ON c.postal = p.postal
WHERE p.postal IS NOT NULL -- < -- removed the row where there's no mathcing result from postalcode
Problemy ze schematem Venna
Internetowe wyszukiwanie obrazów w „sql join cross internal outer” pokaże wiele diagramów Venna. Kiedyś miałem na biurku wydrukowaną kopię. Ale są problemy z reprezentacją.
Schemat Venna doskonale nadaje się do teorii zbiorów, w której element może znajdować się w jednym lub obu zestawach. Ale w przypadku baz danych element w jednym „zestawie” wydaje mi się być wierszem w tabeli, a zatem nie występuje w żadnej innej tabeli. Nie ma czegoś takiego jak jeden wiersz obecny w wielu tabelach. Wiersz jest unikalny dla tabeli.
Połączenia automatyczne to przypadek narożny, w którym każdy element jest w rzeczywistości taki sam w obu zestawach. Ale nadal nie jest wolny od żadnego z poniższych problemów.
Zestaw Areprezentuje zestaw po lewej stronie ( citizentabela), a zestaw Bjest zestawem po prawej stronie ( postalcodetabela) w poniższej dyskusji.
KRZYŻ DOŁĄCZ
Każdy element w obu zestawach jest dopasowany do każdego elementu w drugim zestawie, co oznacza, że potrzebujemy Ailości każdego Belementu i Bilości każdego Aelementu, aby poprawnie reprezentować ten kartezjański produkt. Teoria zbiorów nie jest stworzona dla wielu identycznych elementów w zestawie, więc uważam, że diagramy Venna właściwie przedstawiają to jako niepraktyczne / niemożliwe. Wygląda na to, że UNIONw ogóle nie pasuje.
Rzędy są różne. UNIONJest 7 rzędów w całości. Ale są niekompatybilne dla wspólnego SQLzestawu wyników. I wcale tak nie CROSS JOINdziała:
Próbuję to przedstawić w następujący sposób:
.. ale teraz wygląda to jak coś INTERSECTION, co z pewnością nie jest . Co więcej, nie ma w nim żadnego elementu, INTERSECTIONktóry byłby w żadnym z dwóch odrębnych zestawów. Wygląda jednak bardzo podobnie do wyników wyszukiwania podobnych do tego:
Dla porównania, jeden wynik wyszukiwania dla CROSS JOINs można zobaczyć na Tutorialgateway . Tak INTERSECTIONjak ten, jest pusty.
DOŁĄCZ DO WEWNĘTRZNEGO
Wartość elementu zależy od JOINwarunku. Można to przedstawić pod warunkiem, że każdy wiersz stanie się unikalny dla tego warunku. Znaczenie id=xjest prawdziwe tylko dla jednego wiersza. Gdy wiersz w table A( citizen) pasuje do wielu wierszy w table B( postalcode) pod JOINwarunkiem, wynik ma takie same problemy jak CROSS JOIN: Wiersz musi być reprezentowany wiele razy, a teoria mnogości nie jest do tego stworzona. Pod warunkiem wyjątkowości diagram może jednak działać, ale należy pamiętać, że JOINwarunek określa umiejscowienie elementu na diagramie. Patrząc tylko na wartości JOINwarunku wraz z resztą rzędu tuż przed jazdą:
Reprezentacja ta znajduje się całkowicie od siebie przy używaniu INNER JOINw ON 1 = 1stanie ją sporządza się na klasę CROSS JOIN.
W przypadku self- JOINwiersze są w rzeczywistości identycznymi elementami w obu tabelach, ale reprezentują tabele jako oba Ai Bnie są zbyt odpowiednie. Na przykład powszechnym JOINwarunkiem własnym , który sprawia, że element Ajest dopasowywany do innego elementu w B, jest ON A.parent = B.childdopasowanie od Ado Bna osobnych elementach. Z przykładów, które wyglądałyby SQLtak:
SELECT *
FROM citizen c1
JOIN citizen c2 ON c1.id = c2.leader
Znaczenie Smith jest liderem zarówno Green, jak i Jensen.
DOŁĄCZ DO ZEWNĄTRZ
Ponownie problemy zaczynają się, gdy jeden wiersz ma wiele dopasowań do wierszy w drugiej tabeli. Jest to dodatkowo skomplikowane, ponieważ OUTER JOINmożna dopasować pusty zestaw. Ale w teorii zbiorów połączenie dowolnego zbioru Ci pustego zbioru jest zawsze sprawiedliwe C. Pusty zestaw nic nie dodaje. Reprezentacja tego LEFT OUTER JOINzwykle pokazuje po prostu wszystko, Aaby zilustrować, że wiersze w Asą wybierane niezależnie od tego, czy istnieje dopasowanie, czy nie B. „Dopasowane elementy” mają jednak takie same problemy jak na powyższej ilustracji. Zależą od stanu. I wydaje się, że pusty zestaw wędrował do A:
GDZIE klauzula - sens
Znajdowanie wszystkich wierszy za CROSS JOINpomocą Smitha i kodu pocztowego na Księżycu:
SELECT *
FROM citizen c
CROSS JOIN postalcode p
WHERE c.name = 'Smith'
AND p.area = 'Moon';
Teraz diagram Venna nie jest używany do odzwierciedlenia JOIN. Jest używany tylko w przypadku WHEREklauzuli:
... i to ma sens.
Kiedy INTERSECT i UNION mają sens
KRZYŻOWAĆ
Jak wyjaśniono, INNER JOINtak naprawdę nie jest INTERSECT. Jednak INTERSECTmogą być stosowane s na wynikach oddzielne zapytaniami. Tutaj diagram Venna ma sens, ponieważ elementy z oddzielnych zapytań są w rzeczywistości wierszami, które albo należą do jednego z wyników, albo do obu. Przecięcie oczywiście zwróci wyniki tylko wtedy, gdy wiersz jest obecny w obu zapytaniach. Spowoduje SQLto powstanie tego samego wiersza, co powyższy WHERE, a diagram Venna będzie również taki sam:
SELECT *
FROM citizen c
CROSS JOIN postalcode p
WHERE c.name = 'Smith'
INTERSECT
SELECT *
FROM citizen c
CROSS JOIN postalcode p
WHERE p.area = 'Moon';
UNIA
An OUTER JOINnie jest UNION. Pracuj jednak UNIONw tych samych warunkach co INTERSECT, co powoduje zwrócenie wszystkich wyników łączących oba SELECTs:
SELECT *
FROM citizen c
CROSS JOIN postalcode p
WHERE c.name = 'Smith'
UNION
SELECT *
FROM citizen c
CROSS JOIN postalcode p
WHERE p.area = 'Moon';
co jest równoważne z:
SELECT *
FROM citizen c
CROSS JOIN postalcode p
WHERE c.name = 'Smith'
OR p.area = 'Moon';
.. i daje wynik:
Również tutaj schemat Venna ma sens:
Kiedy to nie dotyczy
Ważną uwagą jest to, że działają one tylko wtedy, gdy struktura wyników z dwóch SELECT jest taka sama, umożliwiając porównanie lub połączenie. Wyniki tych dwóch nie umożliwią:
SELECT *
FROM citizen
WHERE name = 'Smith'
SELECT *
FROM postalcode
WHERE area = 'Moon';
... próba połączenia wyników UNIONdaje
ORA-01790: expression must have same datatype as corresponding expression
W celu dalszego zainteresowania przeczytaj powiedz NIE diagramom Venna podczas wyjaśniania JOIN i złączeń sql jako diagram Venna . Oba obejmują również EXCEPT.
