SQLite Połącz: Naturalny lewy zewnętrzny, wewnętrzny, krzyżowy z przykładami tabel

SQLite obsługuje różne typy SQL Złączenia, 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.

Wprowadzenie do SQLite DOŁĄCZ do klauzuli

Podczas pracy z bazą danych zawierającą wiele tabel często konieczne jest pobranie danych z tych wielu tabel.

Za pomocą klauzuli JOIN możesz 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 łączenia zawiera:

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

Należy pamiętać, że dla wszystkich następujących SQLite Przykłady tabel JOIN: należy uruchomić sqlite3.exe i nawiązać połączenie z przykładową bazą danych w sposób przepływowy:

Krok 1) W tym etapie,

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

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

Teraz możesz uruchomić dowolny typ zapytania w bazie danych.

SQLite INNER JOIN

INNER JOIN zwraca tylko wiersze spełniające warunek złączenia i eliminuje wszystkie inne wiersze, które nie spełniają warunku złączenia.

Przykład

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

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Wyjaśnienie kodu

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

• W klauzuli Select możesz wybrać dowolne kolumny 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 złączenia jest określony za pomocą ON.
• Dla tabel, do których istnieją odniesienia, można określić aliasy.
• Słowo WEWNĘTRZNE jest opcjonalne, możesz po prostu napisać DOŁĄCZ.

Wydajność

• INNER JOIN tworzy rekordy zarówno z tabel studentów, jak i wydziałów, które spełniają warunek „Students.DepartmentId = Departments.DepartmentId „. Niedopasowane wiersze zostaną zignorowane i nieuwzględnione w wyniku.
• Dlatego tylko 8 studentów z 10 zostało zwróconych z tego zapytania z wydziałów IT, matematyki i fizyki. Natomiast studenci „Jena” i „George” nie zostali uwzględnieni, ponieważ mają zerowy identyfikator wydziału, który nie pasuje do kolumny departmentId z tabeli departments. Jak następuje:

  

SQLite DOŁĄCZ… KORZYSTAJĄ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żesz po prostu napisać „USING(DepartmentID)”.

Możesz użyć opcji „JOIN .. USING” zawsze, gdy kolumny, które będziesz porównywać w warunku łączenia, mają tę samą nazwę. W takich przypadkach nie ma potrzeby ich powtarzania z warunkiem włączenia i wystarczy podać nazwy kolumn i SQLite to wykryje.

Różnica między INNER JOIN i JOIN .. KORZYSTANIE:

Za pomocą opcji „JOIN… USING” nie piszesz warunku łączenia, po prostu piszesz kolumnę łączenia, która jest wspólna dla dwóch połączonych tabel, zamiast pisać tabela 1 „INNER JOIN tabela2 ON tabela1.cola = tabela2.cola” piszemy to tak jak „stół 1 DOŁĄCZ do stołu 2 KORZYSTAJĄC (cola)”.

Przykład

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

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
INNER JOIN Departments USING(DepartmentId);

Wyjaśnienie

• W odróżnieniu od poprzedniego przykładu nie napisaliśmy „ON Studenci.DepartmentId = Departments.DepartmentId„. Właśnie napisaliśmy „UŻYWANIE (Identyfikator działu)".
• SQLite automatycznie wnioskuje o warunkach łączenia i porównuje DepartmentId z obu tabel – Students i Departments.
• Możesz użyć tej składni zawsze, gdy dwie porównywane kolumny mają tę samą nazwę.

Wydajność

• Da to dokładnie taki sam wynik, jak w poprzednim przykładzie:

SQLite NATURALNE POŁĄCZENIE

NATURAL JOIN jest podobny do JOIN…USING, z tą różnicą, że automatycznie sprawdza równość wartości każdej kolumny istniejącej w obu tabelach.

Różnica pomiędzy INNER JOIN i NATURAL JOIN:

• W INNER JOIN musisz określić warunek złączenia, którego użyje złączenie wewnętrzne do połączenia dwóch tabel. Podczas gdy w przypadku złączenia naturalnego nie piszesz warunku złączenia. Po prostu wpisz nazwy dwóch tabel bez żadnych warunków. Następnie połączenie naturalne automatycznie sprawdzi równość wartości dla każdej kolumny w obu tabelach. Złączenie naturalne automatycznie określa warunek złączenia.
• W NATURAL JOIN wszystkie kolumny z obu tabel o tej samej nazwie zostaną ze sobą dopasowane. Na przykład, jeśli mamy dwie tabele z dwiema wspólnymi nazwami kolumn (w obu tabelach te dwie kolumny istnieją pod tą samą nazwą), wówczas połączenie naturalne połączy dwie tabele poprzez porównanie wartości obu kolumn, a nie tylko jednej kolumna.

Przykład

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
Natural JOIN Departments;

Wyjaśnienie

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

Wydajność

• Natural JOIN da ci dokładnie taki sam wynik, jaki 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 dla inner join niż dla natural join. Na przykład, jeśli jest więcej tabel o tych samych nazwach, wtedy natural join dopasuje wszystkie kolumny do siebie. Jednak inner join dopasuje tylko kolumny w warunku join (więcej szczegółów w następnej sekcji; różnica między inner join a natural join).

SQLite LEWE POŁĄCZENIE ZEWNĘTRZNE

Standard SQL definiuje trzy typy OUTER JOIN: LEFT, RIGHT i FULL ale SQLite obsługuje tylko naturalne LEWE ŁĄCZENIE ZEWNĘTRZNE.

W LEFT OUTER JOIN wszystkie wartości kolumn wybranych z lewej tabeli zostaną uwzględnione w wyniku pytanie, więc niezależnie od tego, czy wartość odpowiada warunkowi łączenia, czy nie, zostanie uwzględniona w wyniku.

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

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

Przykład

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

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students             -- this is the left table
LEFT JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Wyjaśnienie

• SQLite Składnia LEFT JOIN jest taka sama jak składnia INNER JOIN; piszesz LEFT JOIN pomiędzy dwiema tabelami, a następnie warunek łączenia pojawia się po klauzuli ON.
• Pierwsza tabela po klauzuli from jest tabelą po lewej stronie. Natomiast druga tabela określona po naturalnym LEFT JOIN jest tabelą prawą.
• Klauzula OUTER jest opcjonalna; LEWY naturalny OUTER JOIN jest taki sam jak LEWY JOIN.

Wydajność

• Jak widać, uwzględniono wszystkie wiersze z tabeli uczniów, co daje w sumie 10 uczniów. Nawet jeśli identyfikatory wydziałów czwartego i ostatniego ucznia, Jeny i George'a nie istnieją w tabeli Działy, są one również uwzględnione.
• W takich przypadkach wartość DepartmentName zarówno dla Jeny, jak i dla George'a będzie miała wartość „null”, ponieważ tabela działów nie zawiera atrybutu DepartmentName pasującego do ich wartości DepartmentId.

Wyjaśnijmy poprzedniemu zapytaniu za pomocą lewego złączenia głębsze wyjaśnienie za pomocą diagramów Van:

LEFT JOIN spowoduje podanie nazwisk wszystkich uczniów z tabeli studentów, nawet jeśli student ma identyfikator wydziału, którego nie ma w tabeli wydziałów. Zatem zapytanie nie wyświetli tylko pasujących wierszy jako INNER JOIN, ale da dodatkową część zawierającą niepasujące wiersze z lewej tabeli, czyli tabeli uczniów.

Należy pamiętać, że imię i nazwisko ucznia, które nie ma pasującego wydziału, będzie miało wartość „null” w nazwie wydziału, ponieważ nie ma dla niego pasującej wartości, a te wartości są wartościami w niepasujących wierszach.

SQLite KRZYŻ DOŁĄCZ

POŁĄCZENIE KRZYŻOWE daje iloczyn kartezjański dla wybranych kolumn dwóch połączonych tabel, poprzez dopasowanie wszystkich wartości z pierwszej tabeli do wszystkich wartości z drugiej tabeli.

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

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

SQLite spowoduje uzyskanie logicznych wyników uzyskanych poprzez 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 również zawiera dwie wartości (3,4).

Następnie wynikiem CROSS JOIN będą cztery wiersze:

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

Przykład

W poniższym zapytaniu spróbujemy wykonać połączenie CROSS JOIN pomiędzy tabelami Students i Departments:

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
CROSS JOIN Departments;

Wyjaśnienie

• W SQLite wybierz z wielu tabel, właśnie wybraliśmy dwie kolumny „nazwa studenta” z tabeli studentów i „nazwa wydziału” z tabeli wydziałów.
• W przypadku łączenia krzyżowego nie określiliśmy żadnych warunków łączenia, a jedynie dwie tabele połączone za pomocą CROSS JOIN pośrodku.

Wydajność

Jak widać, wynik to 40 wierszy; 10 wartości z tabeli students dopasowanych do 4 departments z tabeli departments. Jak następuje:

• Cztery wartości dla czterech wydziałów z tabeli wydziałów odpowiadają pierwszemu studentowi Michelowi.
• Cztery wartości dla czterech wydziałów z tabeli działów odpowiadają wartościom drugiego ucznia, Jana.
• Cztery wartości dla czterech wydziałów z tabeli wydziałów odpowiadają trzeciemu uczniowi Jackowi… i tak dalej.

Podsumowanie

Korzystanie z SQLite JOIN, możesz połączyć ze sobą jedną lub więcej tabel lub podzapytań, aby wybrać kolumny z obu tabel lub podzapytań.