Tabele połączeń SQLite: wewnętrzne, naturalne, lewe zewnętrzne, krzyżowe (przykłady)

Spisie treści:

Anonim

SQLite obsługuje różne typy połączeń SQL, takie jak INNER JOIN, LEFT OUTER JOIN i CROSS JOIN. Każdy typ JOIN jest używany w innej sytuacji, jak zobaczymy w tym samouczku.

W tym samouczku nauczysz się:

  • Wprowadzenie do klauzuli SQLite JOIN
  • WEWNĘTRZNE DOŁĄCZENIE
  • DOŁĄCZ… KORZYSTANIE
  • NATURALNE DOŁĄCZENIE
  • POŁĄCZENIE LEWE ZEWNĘTRZNE
  • CROSS JOIN

Wprowadzenie do klauzuli SQLite JOIN

Podczas pracy z bazą danych zawierającą wiele tabel często trzeba uzyskać dane z tych wielu tabel.

Za pomocą klauzuli JOIN można połączyć dwie lub więcej tabel lub podzapytań, łącząc je. Możesz także zdefiniować, według której kolumny chcesz połączyć tabele i według jakich warunków.

Każda klauzula JOIN musi mieć następującą składnię:

Każda klauzula join zawiera:

  • Tabela lub podzapytanie, które jest lewą tabelą; tabela lub podzapytanie przed klauzulą ​​join (po lewej stronie).
  • Operator JOIN - określ typ łączenia (INNER JOIN, LEFT OUTER JOIN lub CROSS JOIN).
  • JOIN-constraint - po określeniu tabel lub podzapytań do połączenia należy określić ograniczenie łączenia, które będzie warunkiem, w którym pasujące wiersze spełniające ten warunek zostaną wybrane w zależności od typu łączenia.

Zauważ, że we wszystkich poniższych przykładach musisz uruchomić sqlite3.exe i otworzyć połączenie z przykładową bazą danych w sposób ciągły:

Krok 1) Na tym etapie

  1. Otwórz Mój komputer i przejdź do następującego katalogu „ C: \ sqlite ” i
  2. Następnie otwórz „ sqlite3.exe ”:

Krok 2) Otwórz bazę danych „ TutorialsSampleDB.db ” za pomocą następującego polecenia:

Teraz jesteś gotowy do uruchamiania dowolnego typu zapytań w bazie danych.

SQLite INNER JOIN

INNER JOIN zwraca tylko te wiersze, które pasują do warunku łączenia i eliminuje wszystkie inne wiersze, które nie są zgodne z warunkiem łączenia.

Przykład

W poniższym przykładzie połączymy dwie tabele „ Studenci ” i „ Działy ” z DepartmentId, aby uzyskać nazwę działu dla każdego ucznia w następujący sposób:

WYBIERZStudents.StudentName,Departments.DepartmentNameOD studentówINNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Wyjaśnienie kodu:

INNER JOIN działa w następujący sposób:

  • W klauzuli Select możesz wybrać dowolne kolumny, które chcesz wybrać z dwóch tabel, do których istnieją odniesienia.
  • Klauzula INNER JOIN jest zapisywana po pierwszej tabeli, do której odwołuje się klauzula „From”.
  • Następnie warunek łączenia jest określany za pomocą ON.
  • Dla tabel, do których istnieją odniesienia, można określić aliasy.
  • Słowo INNER jest opcjonalne, możesz po prostu napisać JOIN.

Wynik:

  • INNER JOIN tworzy rekordy zarówno z tabel studentów, jak i wydziałów, które odpowiadają warunkowi „ S tudents.DepartmentId = Departments.DepartmentId ”. Niedopasowane wiersze zostaną zignorowane i nie zostaną uwzględnione w wyniku.
  • Dlatego tylko 8 uczniów z 10 studentów zostało zwróconych z tego zapytania na wydziałach informatyki, matematyki i fizyki. Natomiast studenci „Jena” i „George” nie zostali uwzględnieni, ponieważ mają pusty identyfikator działu, który nie jest zgodny z kolumną DepartmentId z tabeli działów. W następujący sposób:

SQLite DOŁĄCZ… UŻYWAJĄC

INNER JOIN można zapisać przy użyciu klauzuli „USING”, aby uniknąć nadmiarowości, więc zamiast pisać „ON Students.DepartmentId = Departments.DepartmentId”, można po prostu napisać „USING (DepartmentID)”.

Możesz użyć „JOIN… USING”, gdy kolumny, które chcesz porównać w warunku łączenia, mają tę samą nazwę. W takich przypadkach nie ma potrzeby ich powtarzać przy użyciu warunku on i wystarczy podać nazwy kolumn, a SQLite to wykryje.

Różnica między INNER JOIN a JOIN… UŻYWAJĄC:

Z „JOIN

… UŻYWAJĄC "nie piszesz warunku złączenia, po prostu piszesz kolumnę złączenia, która jest wspólna dla dwóch połączonych tabel, zamiast wpisywać tabelę1" INNER JOIN tabela2 ON table1.cola = table2.cola "piszemy tak jak" table1 JOIN table2 USING (cola) ".

Przykład

W poniższym przykładzie połączymy dwie tabele „ Studenci ” i „ Działy ” z DepartmentId, aby uzyskać nazwę działu dla każdego ucznia w następujący sposób:

WYBIERZStudents.StudentName,Departments.DepartmentNameOD studentówWEWNĘTRZNE DOŁĄCZ Departamenty UŻYWANIE (DepartmentId);

Wyjaśnienie

  • W przeciwieństwie do poprzedniego przykładu nie napisaliśmy „ ON Students.DepartmentId = Departments.DepartmentId ”. Właśnie napisaliśmy „ USING (DepartmentId) ”.
  • SQLite automatycznie wnioskuje o warunku sprzężenia i porównuje DepartmentId z obu tabel - Studenci i Departamenty.
  • Możesz użyć tej składni, gdy dwie porównywane kolumny mają tę samą nazwę.

Wynik

  • Daje to taki sam dokładny wynik, jak w poprzednim przykładzie:

DOŁĄCZ DO SQLite NATURAL

NATURALNE JOIN jest podobne do JOIN… USING, różnica polega na tym, że automatycznie sprawdza równość między wartościami każdej kolumny, która istnieje w obu tabelach.

Różnica między INNER JOIN a NATURAL JOIN:

  • W JOIN WEWNĘTRZNYM musisz określić warunek łączenia, którego używa złączenie wewnętrzne do łączenia dwóch tabel. Podczas gdy w sprzężeniu naturalnym nie piszesz warunku sprzężenia. Po prostu wpisujesz nazwy dwóch tabel bez żadnego warunku. Wtedy sprzężenie naturalne automatycznie sprawdzi równość między wartościami dla każdej kolumny w obu tabelach. Łączenie naturalne automatycznie określa warunek łączenia.
  • W NATURAL JOIN wszystkie kolumny z obu tabel o tej samej nazwie zostaną dopasowane do siebie. Na przykład, jeśli mamy dwie tabele z dwiema wspólnymi nazwami kolumn (dwie kolumny istnieją o tej samej nazwie w dwóch tabelach), to sprzężenie naturalne połączy dwie tabele przez porównanie wartości obu kolumn, a nie tylko jednej kolumna.

Przykład

WYBIERZStudents.StudentName,Departments.DepartmentNameOD studentówNaturalne JOIN Departments;

Wyjaśnienie

  • Nie musimy pisać warunku łączenia z nazwami kolumn (tak jak to zrobiliśmy w INNER JOIN). Nie musieliśmy nawet raz wpisywać nazwy kolumny (tak jak zrobiliśmy to w JOIN USING).
  • Sprzężenie naturalne przeskanuje obie kolumny z dwóch tabel. Wykryje, że warunek powinien składać się z porównania DepartmentId z obu tabel Studenci i Departamenty.

Wynik

  • Natural JOIN da ci dokładnie to samo wyjście, co wyjście, które otrzymaliśmy z przykładów INNER JOIN i JOIN USING. Ponieważ w naszym przykładzie wszystkie trzy zapytania są równoważne. Ale w niektórych przypadkach wynik będzie inny niż sprzężenie wewnętrzne niż sprzężenie naturalne. Na przykład, jeśli istnieje więcej tabel o tych samych nazwach, sprzężenie naturalne dopasuje wszystkie kolumny do siebie. Jednak sprzężenie wewnętrzne dopasuje tylko kolumny w warunku sprzężenia (więcej szczegółów w następnej sekcji; różnica między sprzężeniem wewnętrznym a sprzężeniem naturalnym).

SQLite LEFT OUTER JOIN

Standard SQL definiuje trzy typy POŁĄCZEŃ ZEWNĘTRZNYCH: LEFT, RIGHT i FULL, ale SQLite obsługuje tylko LEFT OUTER JOIN.

W LEFT OUTER JOIN wszystkie wartości kolumn wybranych z lewej tabeli zostaną uwzględnione w wyniku zapytania, więc niezależnie od tego, czy wartość pasuje do warunku łączenia, czy nie, zostaną uwzględnione w wyniku.

Jeśli więc lewa tabela ma „n” wierszy, wyniki zapytania będą miały „n” wierszy. Jednak dla wartości kolumn pochodzących z prawej tabeli, jeśli jakakolwiek wartość, która nie pasuje do warunku łączenia, będzie zawierała wartość „null”.

Otrzymasz więc liczbę wierszy odpowiadającą liczbie wierszy w lewym złączeniu. Dzięki temu otrzymasz pasujące wiersze z obu tabel (np. Wyniki INNER JOIN) oraz niepasujące wiersze z lewej tabeli.

Przykład

W poniższym przykładzie spróbujemy „LEFT JOIN” połączyć dwie tabele „Studenci” i „Działy”:

WYBIERZStudents.StudentName,Departments.DepartmentNameOD Studentów - to jest lewa tabelaLEFT JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Wyjaśnienie

  • Składnia LEFT JOIN jest taka sama jak INNER JOIN; piszesz LEFT JOIN między dwiema tabelami, a następnie warunek łączenia pojawia się po klauzuli ON.
  • Pierwsza tabela po klauzuli from to lewa tabela. Natomiast druga tabela określona po lewym złączeniu jest tabelą po prawej stronie.
  • Klauzula OUTER jest opcjonalna; LEFT OUTER JOIN jest tym samym, co LEFT JOIN.

Wynik

  • Jak widać, uwzględniono wszystkie wiersze z tabeli uczniów, czyli łącznie 10 uczniów. Nawet jeśli identyfikatory wydziałów czwartego i ostatniego ucznia, Jeny i George'a, nie istnieją w tabeli Departments, są one również uwzględniane.
  • W takich przypadkach wartość DepartmentName zarówno dla Jeny, jak i George'a będzie miała wartość „null”, ponieważ tabela departamentów nie ma nazwy działu, która jest zgodna z ich wartością ID działu.

Dajmy poprzedniemu zapytaniu, używając lewej złączki, głębsze wyjaśnienie za pomocą diagramów Van:

LEWY DOŁĄCZ poda imiona wszystkich studentów z tabeli studentów, nawet jeśli student ma identyfikator wydziału, którego nie ma w tabeli działów. Tak więc zapytanie nie da ci tylko pasujących wierszy jako INNER JOIN, ale da ci dodatkową część, która ma niezgodne wiersze z lewej tabeli, czyli tabeli uczniów.

Zwróć uwagę, że każde nazwisko ucznia, które nie ma pasującego działu, będzie miało wartość „null” dla nazwy działu, ponieważ nie ma dla niego pasującej wartości, a te wartości są wartościami w niezgodnych wierszach.

SQLite CROSS JOIN

CROSS JOIN daje iloczyn kartezjański dla wybranych kolumn dwóch połączonych tabel, dopasowując wszystkie wartości z pierwszej tabeli do wszystkich wartości z drugiej tabeli.

Tak więc dla każdej wartości w pierwszej tabeli otrzymasz „n” dopasowań z drugiej tabeli, gdzie n to liczba drugich wierszy tabeli.

W przeciwieństwie do INNER JOIN i LEFT OUTER JOIN, z CROSS JOIN nie musisz określać warunku złączenia, ponieważ SQLite nie potrzebuje go do CROSS JOIN.

SQLite da w wyniku logiczne wyniki ustawione przez połączenie wszystkich wartości z pierwszej tabeli ze wszystkimi wartościami z drugiej tabeli.

Na przykład, jeśli wybrałeś kolumnę z pierwszej tabeli (colA) i inną kolumnę z drugiej tabeli (colB). ColA zawiera dwie wartości (1,2), a colB zawiera również dwie wartości (3,4).

Wtedy wynikiem CROSS JOIN będą cztery rzędy:

  • Dwa wiersze poprzez połączenie pierwszej wartości z colA, czyli 1, z dwoma wartościami z colB (3,4), które będą (1,3), (1,4).
  • Podobnie dwa wiersze, łącząc drugą wartość z colA, która wynosi 2, z dwoma wartościami z kolumny B (3,4), którymi są (2,3), (2,4).

Przykład

W poniższym zapytaniu spróbujemy CROSS JOIN między tabelami Students i Departments:

WYBIERZStudents.StudentName,Departments.DepartmentNameOD studentówCROSS JOIN Departamenty;

Wyjaśnienie

  • W klauzuli select wybraliśmy właśnie dwie kolumny „studentname” z tabeli studentów oraz „nazwa wydziału” z tabeli działów.
  • W przypadku łączenia krzyżowego nie określiliśmy żadnego warunku łączenia, tylko dwie tabele połączone z CROSS JOIN w środku nich.

Wynik:

Jak widać, wynik to 40 wierszy; 10 wartości z tabeli studentów dopasowanych do 4 wydziałów z tabeli działów. W następujący sposób:

  • Cztery wartości dla czterech wydziałów z tabeli działów dopasowane do pierwszego ucznia Michela.
  • Cztery wartości dla czterech działów z tabeli działów dopasowane do drugiego ucznia, Jana.
  • Cztery wartości dla czterech wydziałów z tabeli działów dopasowane do trzeciego ucznia Jacka.

    … i tak dalej.

Podsumowanie

Korzystając z SQLite JOIN, można połączyć jedną lub więcej tabel lub podzapytań, aby wybrać kolumny z obu tabel lub podzapytań.