SQLite Połącz: Naturalne lewe zewnętrzne, wewnętrzne, krzyżowe z tabelami

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 kroku otwórz Mój komputer i przejdź do następującego katalogu „C:\sqlite”, a następnie otwórz „sqlite3.exe“:

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

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

SQLite INNER JOIN

Operacja INNER JOIN zwraca tylko wiersze spełniające warunek połączenia i eliminuje wszystkie pozostałe wiersze, które nie spełniają warunku połączenia.

Przykład

W poniższym przykładzie połączymy dwie tabele „Students” i „Departments” za pomocą DepartmentId, aby uzyskać nazwę wydziału dla każdego studenta, 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ść

Operacja INNER JOIN generuje rekordy z obu tabel – studentów i wydziałów – które spełniają warunek „Students.DepartmentId = Departments.DepartmentId”. Niedopasowane wiersze zostaną zignorowane i nie zostaną uwzględnione w wyniku.

Dlatego w wyniku tego zapytania zwrócono tylko 8 studentów z 10 wydziałów informatyki, matematyki i fizyki. Natomiast studenci „Jena” i „George” nie zostali uwzględnieni, ponieważ ich identyfikatory wydziału są zerowe i nie pasują do kolumny „departmentId” w tabeli „departments”. Poniżej:

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 pomiędzy INNER JOIN i JOIN... UŻYCIE:

W przypadku polecenia „JOIN … USING” nie należy pisać warunku łączenia, wystarczy wpisać kolumnę łączenia, która jest wspólna dla obu łączonych tabel. Zamiast pisać tabela1 „INNER JOIN table2 ON table1.cola = table2.cola” zapisujemy to tak: „table1 JOIN table2 USING(cola)”.

Przykład

W poniższym przykładzie połączymy dwie tabele „Students” i „Departments” za pomocą DepartmentId, aby uzyskać nazwę wydziału dla każdego studenta, w następujący sposób:

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

Wyjaśnienie

• W przeciwieństwie do poprzedniego przykładu, nie napisaliśmy „ON Students.DepartmentId = Departments.DepartmentId”. Napisaliśmy po prostu „USING(DepartmentId)”.
• 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 przypadku INNER JOIN należy określić warunek złączenia, którego sprzężenie wewnętrzne używa do połączenia dwóch tabel. Natomiast w przypadku sprzężenia naturalnego nie tworzy się warunku złączenia. Wystarczy wpisać nazwy dwóch tabel bez żadnego warunku. Następnie sprzężenie naturalne automatycznie sprawdzi równość wartości dla każdej kolumny w obu tabelach. Spojrzenie naturalne automatycznie wnioskuje o warunku 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 łączenia z nazwami kolumn (jak w przypadku INNER JOIN). Nie musieliśmy nawet ani razu wpisywać nazwy kolumny (jak w przypadku 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ść

Połączenie NATURAL JOIN da dokładnie taki sam wynik, jak w przypadku połączeń INNER JOIN i JOIN USING, ponieważ w naszym przykładzie wszystkie trzy zapytania są równoważne. Jednak w niektórych przypadkach wynik połączenia wewnętrznego będzie inny niż połączenia naturalnego. Na przykład, jeśli istnieje więcej tabel o tych samych nazwach, połączenie naturalne dopasuje wszystkie kolumny do siebie. Natomiast połączenie wewnętrzne dopasuje tylko kolumny w warunku połączenia.

SQLite LEWE POŁĄCZENIE ZEWNĘTRZNE

Standard SQL definiuje trzy typy połączeń zewnętrznych: LEFT, RIGHT i FULL, ale SQLite obsługuje tylko naturalne LEWE ŁĄCZENIE ZEWNĘTRZNE.

W przypadku LEFT OUTER JOIN wszystkie wartości kolumn wybranych z tabeli po lewej stronie zostaną uwzględnione w wyniku zapytania. Oznacza to, że niezależnie od tego, czy wartość spełnia warunek połączenia, czy nie, zostanie ona uwzględniona w wyniku.

Zatem jeśli lewa tabela ma „n” wierszy, wyniki zapytania również będą miały „n” wierszy. Jednak w przypadku wartości kolumn pochodzących z prawej tabeli, jeśli jakakolwiek wartość nie spełnia warunku łączenia, będzie zawierała 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 „studenci”, co daje łącznie 10 studentów. Nawet jeśli czwarty i ostatni student, Jena i George, mają identyfikatory wydziałów, których nie ma w tabeli „Departamenty”, również są uwzględniane.

W takich przypadkach wartość departmentName zarówno dla Jeny, jak i George'a będzie równa „null”, ponieważ tabela departments nie zawiera wartości departmentName odpowiadającej wartości departmentId.

Wyjaśnijmy poprzednie zapytanie, wykorzystując połączenie lewe, bardziej szczegółowo, korzystając z diagramów Venna:

LEFT JOIN zwróci nazwiska wszystkich studentów z tabeli „students”, nawet jeśli student ma identyfikator wydziału, który nie występuje w tabeli „departments”. Zatem zapytanie nie zwróci tylko pasujących wierszy, jak w przypadku INNER JOIN, ale także dodatkową część, która zawiera niepasujące wiersze z lewej tabeli, czyli tabeli „students”.

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 trzeba określać warunku połączenia, ponieważ SQLite nie jest to potrzebne do CROSS JOIN.

SQLite spowoduje utworzenie logicznego zestawu wyników poprzez połączenie wszystkich wartości z pierwszej tabeli ze wszystkimi wartościami z drugiej tabeli.

Na przykład, jeśli wybierzesz kolumnę z pierwszej tabeli (colA) i inną kolumnę z drugiej tabeli (colB), kolumna colA zawiera dwie wartości (1,2), a kolumna 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 połączenia krzyżowego nie określiliśmy żadnego warunku połączenia, jedynie połączyliśmy dwie tabele za pomocą połączenia krzyżowego CROSS JOIN w ś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 wydziałów dopasowano do drugiego studenta, Johna.
• Cztery wartości dla czterech wydziałów z tabeli wydziałów dopasowano do trzeciego studenta, Jacka… i tak dalej.

FAQ