SQLite Spojení: Přirozené levé vnější, vnitřní, křížové s tabulkami

SQLite podporuje různé typy SQL Spojení, jako INNER JOIN, LEFT OUTER JOIN a CROSS JOIN. Každý typ JOIN se používá pro jinou situaci, jak uvidíme v tomto tutoriálu.

Úvod do SQLite Klauzule JOIN

Když pracujete na databázi s více tabulkami, často potřebujete získat data z těchto více tabulek.

Pomocí klauzule JOIN můžete propojit dvě nebo více tabulek nebo poddotazů jejich spojením. Můžete také definovat, podle kterého sloupce potřebujete propojit tabulky a podle jakých podmínek.

Každá klauzule JOIN musí mít následující syntaxi:

Každá spojovací klauzule obsahuje:

• Tabulka nebo poddotaz, což je levá tabulka; tabulku nebo poddotaz před klauzulí spojení (nalevo od ní).
• Operátor JOIN – zadejte typ spojení (buď INNER JOIN, LEFT OUTER JOIN nebo CROSS JOIN).
• Omezení JOIN – poté, co jste zadali tabulky nebo poddotazy, které se mají spojit, musíte zadat omezení spojení, což bude podmínka, na jejímž základě budou vybrány odpovídající řádky odpovídající této podmínce v závislosti na typu spojení.

Všimněte si, že pro všechny následující SQLite Příklady tabulek JOIN, musíte spustit sqlite3.exe a otevřít připojení k ukázkové databázi jako plynulé:

Krok 1) V tomto kroku otevřete složku Tento počítač, přejděte do adresáře „C:\sqlite“ a poté otevřete soubor „sqlite3.exe“:

Krok 2) Otevřete databázi „TutorialsSampleDB.db“ pomocí následujícího příkazu:

Nyní jste připraveni spustit jakýkoli typ dotazu na databázi.

SQLite INNER JOIN

Funkce INNER JOIN vrací pouze řádky, které splňují podmínku spojení, a eliminuje všechny ostatní řádky, které podmínce spojení neodpovídají.

Příklad

V následujícím příkladu spojíme dvě tabulky „Studenti“ a „Katedry“ pomocí DepartmentId, abychom získali název katedry pro každého studenta takto:

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

Vysvětlení kódu

INNER JOIN funguje následovně:

• V klauzuli Select můžete vybrat libovolné sloupce, které chcete vybrat, ze dvou odkazovaných tabulek.
• Klauzule INNER JOIN se píše za první tabulkou, na kterou se odkazuje klauzulí „From“.
• Potom je podmínka spojení specifikována pomocí ON.
• Pro odkazované tabulky lze zadat aliasy.
• Slovo INNER je nepovinné, stačí napsat JOIN.

Výstup

INNER JOIN vygeneruje záznamy z tabulek studentů i oddělení, které splňují podmínku „Students.DepartmentId = Departments.DepartmentId“. Neodpovídající řádky budou ignorovány a nebudou zahrnuty do výsledku.

Proto se z tohoto dotazu našlo pouze 8 studentů z 10 s katedrami IT, matematiky a fyziky. Studenti „Jena“ a „George“ nebyli zahrnuti, protože mají null departmentId, což se neshoduje se sloupcem departmentId z tabulky departments. Následuje:

SQLite PŘIPOJTE SE … POUŽÍVÁNÍM

INNER JOIN lze napsat pomocí klauzule „USING“, aby se předešlo nadbytečnosti, takže místo psaní „ON Students.DepartmentId = Departments.DepartmentId“ můžete napsat pouze „USING(DepartmentID)“.

„JOIN .. USING“ můžete použít vždy, když budou mít sloupce, které budete porovnávat v podmínce spojení, stejný název. V takových případech není potřeba je opakovat pomocí on podmínky a stačí uvést názvy sloupců a SQLite to zjistí.

Rozdíl mezi INNER JOIN a JOIN .. POUŽITÍ:

S „JOIN … USING“ nezapíšete podmínku spojení, pouze zapíšete sloupec spojení, který je společný pro dvě spojená pole. Místo zápisu table1 „INNER JOIN table2 ON table1.cola = table2.cola“ to zapíšeme jako „table1 JOIN table2 USING(cola)“.

Příklad

V následujícím příkladu spojíme dvě tabulky „Studenti“ a „Katedry“ pomocí DepartmentId, abychom získali název katedry pro každého studenta takto:

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

Vysvětlení

• Na rozdíl od předchozího příkladu jsme nenapsali „ON Students.DepartmentId = Departments.DepartmentId“. Napsali jsme pouze „USING(DepartmentId)“.
• SQLite automaticky odvodí podmínku spojení a porovná DepartmentId z obou tabulek – Students a Departments.
• Tuto syntaxi můžete použít vždy, když mají dva porovnávané sloupce stejný název.

Výstup

Získáte stejný přesný výsledek jako předchozí příklad:

SQLite PŘIROZENÉ SPOJENÍ

NATURAL JOIN je podobný JOIN…USING, rozdíl je v tom, že automaticky testuje rovnost mezi hodnotami každého sloupce, který existuje v obou tabulkách.

Rozdíl mezi INNER JOIN a NATURAL JOIN:

• V INNER JOIN musíte zadat podmínku spojení, kterou vnitřní spojení použije ke spojení dvou tabulek. Zatímco v přirozeném spojení se podmínka spojení nezadává. Stačí zapsat názvy dvou tabulek bez jakékoli podmínky. Přirozené spojení pak automaticky otestuje shodu hodnot pro každý sloupec v obou tabulkách. Přirozené spojení automaticky odvodí podmínku spojení.
• V NATURAL JOIN budou porovnány všechny sloupce z obou tabulek se stejným názvem. Například, pokud máme dvě tabulky se dvěma společnými názvy sloupců (dva sloupce existují se stejným názvem ve dvou tabulkách), přirozené spojení spojí dvě tabulky porovnáním hodnot obou sloupců a nikoli pouze z jednoho. sloupec.

Příklad

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

Vysvětlení

• Nemusíme psát podmínku spojení s názvy sloupců (jako jsme to udělali v INNER JOIN). Ani jsme nemuseli psát název sloupce jednou (jako jsme to udělali v JOIN USING).
• Přirozené spojení prohledá oba sloupce ze dvou tabulek. Zjistí, že podmínka by měla být složena z porovnání DepartmentId ze dvou tabulek Students a Departments.

Výstup

Příkaz PŘIROZENÝ JOIN vám dá přesně stejný výstup jako výstup, který jsme dostali z příkladů INNER JOIN a JOIN USING, protože v našem příkladu jsou všechny tři dotazy ekvivalentní. V některých případech se však výstup z vnitřního spojení bude lišit od výstupu z přirozeného spojení. Například pokud existuje více tabulek se stejnými názvy, pak přirozené spojení porovná všechny sloupce. Vnitřní spojení však porovná pouze sloupce v podmínce spojení.

SQLite LEVÁ VNĚJŠÍ SPOJENÍ

Standard SQL definuje tři typy vnějších spojení (OUTER JOIN): levé (LEFT), pravé (RIGHT) a úplné (FULL), ale SQLite podporuje pouze přirozený LEFT OUTER JOIN.

V levém vnějším spojení budou všechny hodnoty sloupců, které vyberete z levé tabulky, zahrnuty do výsledku dotazu, takže bez ohledu na to, zda hodnota splňuje podmínku spojení, bude zahrnuta do výsledku.

Takže pokud má levá tabulka 'n' řádků, výsledky dotazu budou mít 'n' řádků. Nicméně pro hodnoty sloupců pocházejících z pravé tabulky, pokud jakákoli hodnota nesplňuje podmínku spojení, bude obsahovat hodnotu „null“.

Získáte tak počet řádků ekvivalentní počtu řádků v levém spojení. Takže získáte odpovídající řádky z obou tabulek (jako výsledky INNER JOIN) plus neodpovídající řádky z levé tabulky.

Příklad

V následujícím příkladu vyzkoušíme spojení „LEFT JOIN“ pro spojení dvou tabulek „Students“ a „Departments“:

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

Vysvětlení

• SQLite Syntaxe LEFT JOIN je stejná jako INNER JOIN; zapíšete LEFT JOIN mezi dvě tabulky a potom podmínka spojení následuje po klauzuli ON.
• První tabulka po klauzuli from je levá tabulka. Zatímco druhá tabulka zadaná po přirozeném LEFT JOIN je pravá tabulka.
• Klauzule OUTER je volitelná; LEFT natural OUTER JOIN je totéž jako LEFT JOIN.

Výstup

Jak vidíte, zahrnuty jsou všechny řádky z tabulky studentů, což je celkem 10 studentů. I když čtvrtý a poslední student, Jena a George, mají ID oddělení, která v tabulce Oddělení neexistují, jsou také zahrnuti.

A v těchto případech bude hodnota departmentName pro Jenu i George „null“, protože tabulka departments neobsahuje hodnotu departmentName, která by odpovídala jejich hodnotě departmentId.

Vysvětlíme si předchozí dotaz s použitím levého spojení podrobněji pomocí Vennových diagramů:

LEFT JOIN vrátí všechna jména studentů z tabulky studenti, i když student má ID oddělení, které v tabulce oddělení neexistuje. Dotaz tedy nevrátí pouze odpovídající řádky jako INNER JOIN, ale vrátí i část navíc, která obsahuje neshodné řádky z levé tabulky, což je tabulka studenti.

Všimněte si, že každé jméno studenta, které nemá žádné odpovídající oddělení, bude mít pro název oddělení hodnotu „null“, protože pro něj neexistuje žádná odpovídající hodnota a tyto hodnoty jsou hodnoty v neodpovídajících řádcích.

SQLite KRÍŽNÍ PŘIPOJENÍ

CROSS JOIN dává kartézský součin pro vybrané sloupce dvou spojených tabulek, a to porovnáním všech hodnot z první tabulky se všemi hodnotami z druhé tabulky.

Takže pro každou hodnotu v první tabulce dostanete 'n' shod z druhé tabulky, kde n je počet druhých řádků tabulky.

Na rozdíl od INNER JOIN a LEFT OUTER JOIN, u CROSS JOIN nemusíte zadávat podmínku spojení, protože SQLite nepotřebuje to pro CROSS JOIN.

Jedno SQLite výsledkem bude logická sada výsledků kombinací všech hodnot z první tabulky se všemi hodnotami z druhé tabulky.

Například pokud jste vybrali sloupec z první tabulky (colA) a další sloupec z druhé tabulky (colB), sloupec A obsahuje dvě hodnoty (1,2) a sloupec B také obsahuje dvě hodnoty (3,4).

Pak výsledkem CROSS JOIN budou čtyři řádky:

• Dva řádky kombinací první hodnoty z colA, která je 1, se dvěma hodnotami colB (3,4), což bude (1,3), (1,4).
• Podobně dva řádky kombinací druhé hodnoty z colA, která je 2, se dvěma hodnotami colB (3,4), což jsou (2,3), (2,4).

Příklad

V následujícím dotazu vyzkoušíme CROSS JOIN mezi tabulkami Studenti a Katedry:

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

Vysvětlení

• v SQLite výběr z více tabulek, právě jsme vybrali dva sloupce „studentname“ z tabulky studentů a „departmentName“ z tabulky oddělení.
• Pro křížové spojení jsme neurčili žádnou podmínku spojení, pouze dvě tabulky zkombinované pomocí CROSS JOIN uprostřed nich.

Výstup

Jak vidíte, výsledkem je 40 řádků; 10 hodnot z tabulky studentů se shodovalo se 4 hodnotami z tabulky oddělení. Takto:

• Čtyři hodnoty pro čtyři oddělení z tabulky oddělení odpovídaly prvnímu studentovi Michelovi.
• Čtyři hodnoty pro čtyři oddělení z tabulky oddělení se shodovaly s druhým studentem Janem.
• Čtyři hodnoty pro čtyři oddělení z tabulky oddělení se shodovaly s třetím studentem Jackem… a tak dále.

Nejčastější dotazy