SQLite Съединение: Естествен ляв външен, вътрешен, кръст с пример за таблици

по: Маркъс Алън Маркъс Алън
Hours Обновено

SQLite поддържа различни видове SQL Съединения, като INNER JOIN, LEFT OUTER JOIN и CROSS JOIN. Всеки тип JOIN се използва за различна ситуация, както ще видим в този урок.

Въведение в SQLite Клауза JOIN

Когато работите върху база данни с множество таблици, често трябва да получите данни от тези множество таблици.

С клаузата JOIN можете да свържете две или повече таблици или подзаявки, като ги съедините. Също така можете да определите по коя колона трябва да свържете таблиците и по какви условия.

Всяка JOIN клауза трябва да има следния синтаксис:

SQLite Синтаксис на клауза JOIN
SQLite Синтаксис на клауза JOIN

Всяка клауза за присъединяване съдържа:

  • Таблица или подзаявка, която е лявата таблица; таблицата или подзаявката преди клаузата за присъединяване (вляво от нея).
  • Оператор JOIN – задайте типа на съединяването (или INNER JOIN, LEFT OUTER JOIN или CROSS JOIN).
  • JOIN-ограничение – след като сте посочили таблиците или подзаявките за присъединяване, трябва да посочите ограничение за присъединяване, което ще бъде условие, при което съответстващите редове, които отговарят на това условие, ще бъдат избрани в зависимост от типа на присъединяването.

Имайте предвид, че за всички следващи SQLite Примери за JOIN таблици, трябва да стартирате sqlite3.exe и да отворите връзка към примерната база данни като течаща:

Стъпка 1) В този етап,

  1. Отворете Моят компютър и отидете до следната директория „C:\sqlite" и
  2. След това отворете „sqlite3.exe"

SQLite Клауза JOIN

Стъпка 2) Отворете базата данни "УроциSampleDB.db” чрез следната команда:

SQLite Клауза JOIN

Сега сте готови да изпълните всякакъв тип заявка в базата данни.

SQLite ВЪВЕЖДАНЕ

INNER JOIN връща само редовете, които отговарят на условието за присъединяване, и елиминира всички други редове, които не отговарят на условието за присъединяване.

SQLite ВЪВЕЖДАНЕ
SQLite ВЪВЕЖДАНЕ

Пример

В следващия пример ще обединим двете таблици "Студенти"А"Катедри” с DepartmentId, за да получите името на отдела за всеки студент, както следва:

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

Обяснение на кода

INNER JOIN работи по следния начин:

  • В клаузата Select можете да изберете каквито колони искате да изберете от двете посочени таблици.
  • Клаузата INNER JOIN е написана след първата таблица, посочена с клауза „От“.
  • Тогава условието за присъединяване се определя с ON.
  • Псевдонимите могат да бъдат зададени за референтни таблици.
  • Думата INNER не е задължителна, можете просто да напишете JOIN.

Продукция

SQLite INNER JOIN Пример

  • INNER JOIN създава записите както от студентите, така и от таблиците на отдела, които отговарят на условието, което е „Students.DepartmentId = Отдели.DepartmentId “. Несъответстващите редове ще бъдат игнорирани и няма да бъдат включени в резултата.
  • Ето защо само 8 ученици от 10 ученици бяха върнати от тази заявка с отдели по ИТ, математика и физика. Докато студентите „Jena“ и „George“ не бяха включени, защото имат нулев идентификатор на отдел, който не съвпада с колоната departmentId от таблицата с отдели. Както следва:

    SQLite INNER JOIN Пример

СВЪРЗАНИ СТАТИИ

SQLite ПРИСЪЕДИНЕТЕ СЕ ... ИЗПОЛЗВАНЕТО

INNER JOIN може да бъде написан с помощта на клаузата „USING“, за да се избегне излишък, така че вместо да пишете „ON Students.DepartmentId = Departments.DepartmentId“, можете просто да напишете „USING(DepartmentID)“.

Можете да използвате „JOIN .. USING“ винаги, когато колоните, които ще сравнявате в условието за присъединяване, са с едно и също име. В такива случаи няма нужда да ги повтаряте с помощта на условието и просто посочете имената на колоните и SQLite ще открие това.

Разликата между INNER JOIN и JOIN .. ИЗПОЛЗВАНЕ:

С “JOIN … USING” вие не пишете условие за присъединяване, вие просто пишете колоната за присъединяване, която е обща между двете обединени таблици, вместо да пишете table1 “INNER JOIN table2 ON table1.cola = table2.cola” пишем като „table1 JOIN table2 ИЗПОЛЗВАНЕ (кола)“.

Пример

В следващия пример ще обединим двете таблици "Студенти"А"Катедри” с DepartmentId, за да получите името на отдела за всеки студент, както следва:

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

Обяснение

  • За разлика от предишния пример, ние не написахме „ON Students.DepartmentId = Departments.DepartmentId“. Току-що написахме "ИЗПОЛЗВАНЕ (ИД на отдел)".
  • SQLite извежда автоматично условието за присъединяване и сравнява DepartmentId от двете таблици – Students и Departments.
  • Можете да използвате този синтаксис винаги, когато двете колони, които сравнявате, са с едно и също име.

Продукция

  • Това ще ви даде същия точен резултат като предишния пример:

SQLite ПРИСЪЕДИНЯВАНЕ Пример

SQLite ЕСТЕСТВЕНО СЪЕДИНЕНИЕ

NATURAL JOIN е подобно на JOIN…USING, разликата е, че автоматично тества за равенство между стойностите на всяка колона, която съществува в двете таблици.

Разликата между INNER JOIN и NATURAL JOIN:

  • В INNER JOIN трябва да зададете условие за свързване, което вътрешното съединение използва за свързване на двете таблици. Докато при естественото присъединяване вие ​​не пишете условие за присъединяване. Просто пишете имената на двете таблици без никакви условия. Тогава естественото съединение автоматично ще тества за равенство между стойностите за всяка колона, съществуваща в двете таблици. Естественото присъединяване автоматично извежда условието за присъединяване.
  • В NATURAL JOIN всички колони от двете таблици с едно и също име ще бъдат съпоставени една с друга. Например, ако имаме две таблици с две общи имена на колони (двете колони съществуват с едно и също име в двете таблици), тогава естественото съединение ще обедини двете таблици чрез сравняване на стойностите на двете колони, а не само от една колона.

Пример

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

Обяснение

  • Не е необходимо да пишем условие за свързване с имена на колони (както направихме в INNER JOIN). Дори не беше необходимо да пишем името на колоната веднъж (както направихме в JOIN USING).
  • Естественото съединение ще сканира и двете колони от двете таблици. Той ще открие, че условието трябва да се състои от сравняване на DepartmentId от двете таблици Students и Departments.

Продукция

SQLite Пример за ЕСТЕСТВЕНО СЪЕДИНЯВАНЕ

  • Natural JOIN ще ви даде същия точен изход като изхода, който получихме от примерите INNER JOIN и JOIN USING. Тъй като в нашия пример и трите заявки са еквивалентни. Но в някои случаи изходът ще бъде различен от вътрешното съединение, отколкото в естественото съединение. Например, ако има повече таблици с еднакви имена, тогава естественото съединение ще съпостави всички колони една срещу друга. Вътрешното съединение обаче ще съответства само на колоните в условието за свързване (повече подробности в следващия раздел; разликата между вътрешното съединение и естественото съединение).

SQLite ЛЯВО ВЪНШНО СЪЕДИНЕНИЕ

Стандартът SQL дефинира три типа ВЪНШНИ СЪЕДИНЕНИЯ: ЛЯВО, ДЯСНО и ПЪЛНО, но SQLite поддържа само естественото LEFT OUTER JOIN.

В LEFT OUTER JOIN всички стойности на колоните, които изберете от лявата таблица, ще бъдат включени в резултата от заявка, така че независимо дали стойността съответства на условието за присъединяване или не, тя ще бъде включена в резултата.

Така че, ако лявата таблица има 'n' реда, резултатите от заявката ще имат 'n' реда. Въпреки това, за стойностите на колоните, идващи от правилната таблица, ако има стойност, която не съответства на условието за присъединяване, тя ще съдържа „нулева“ стойност.

Така че ще получите брой редове, еквивалентен на броя редове в лявото съединение. Така че ще получите съвпадащите редове от двете таблици (като резултатите от INNER JOIN), плюс несъвпадащите редове от лявата таблица.

Пример

В следващия пример ще опитаме „LEFT JOIN“, за да обединим двете таблици „Студенти“ и „Отдели“:

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

Обяснение

  • SQLite Синтаксисът на LEFT JOIN е същият като INNER JOIN; пишете LEFT JOIN между двете таблици и след това условието за свързване идва след клаузата ON.
  • Първата таблица след клаузата from е лявата таблица. Докато втората таблица, посочена след естествения LEFT JOIN, е дясната таблица.
  • Клаузата OUTER не е задължителна; LEFT natural OUTER JOIN е същото като LEFT JOIN.

Продукция

SQLite ЛЯВО ВЪНШНО СЪЕДИНЕНИЕ Пример

  • Както можете да видите, всички редове от таблицата на учениците са включени, което е общо 10 ученици. Дори ако четвъртият и последният студент, Jena и George departmentIds не съществуват в таблицата Departments, те също са включени.
  • И в тези случаи стойността departmentName както за Jena, така и за George ще бъде „нулева“, защото таблицата с отдели няма departmentName, което да съответства на тяхната стойност departmentId.

Нека да дадем по-задълбочено обяснение на предишната заявка, използвайки лявото съединение, използвайки диаграми на Ван:

SQLite ЛЯВО ВЪНШНО СЪЕДИНЕНИЕ

SQLite ЛЯВО ВЪНШНО СЪЕДИНЕНИЕ

LEFT JOIN ще даде имената на всички студенти от таблицата на студентите, дори ако студентът има идентификатор на отдел, който не съществува в таблицата на отделите. Така че заявката няма да ви даде само съответстващите редове като INNER JOIN, но ще ви даде допълнителната част, която има несъвпадащите редове от лявата таблица, която е таблицата на учениците.

Обърнете внимание, че всяко име на ученик, което няма съответстващ отдел, ще има „нулева“ стойност за името на отдела, тъй като няма съвпадаща стойност за него и тези стойности са стойностите в несъвпадащите редове.

SQLite КРЪСТОСТНА СЪЕДИНКА

CROSS JOIN дава декартово произведение за избраните колони на двете обединени таблици, като съпоставя всички стойности от първата таблица с всички стойности от втората таблица.

Така че за всяка стойност в първата таблица ще получите 'n' съвпадения от втората таблица, където n е броят на редовете на втората таблица.

За разлика от INNER JOIN и LEFT OUTER JOIN, с CROSS JOIN не е необходимо да указвате условие за свързване, т.к. SQLite не се нуждае от него за CROSS JOIN.

- SQLite ще доведе до логически набор от резултати чрез комбиниране на всички стойности от първата таблица с всички стойности от втората таблица.

Например, ако сте избрали колона от първата таблица (colA) и друга колона от втората таблица (colB). colA съдържа две стойности (1,2), а colB също съдържа две стойности (3,4).

Тогава резултатът от CROSS JOIN ще бъде четири реда:

  • Два реда чрез комбиниране на първата стойност от colA, която е 1, с двете стойности на colB (3,4), които ще бъдат (1,3), (1,4).
  • По същия начин два реда чрез комбиниране на втората стойност от colA, която е 2, с двете стойности на colB (3,4), които са (2,3), (2,4).

Пример

В следната заявка ще опитаме CROSS JOIN между таблиците Students и Departments:

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

Обяснение

  • в SQLite изберете от множество таблици, ние току-що избрахме две колони „studentname“ от таблицата „students“ и „departmentName“ от таблицата с отдели.
  • За кръстосаното свързване не сме посочили никакво условие за свързване, а само двете таблици, комбинирани с CROSS JOIN в средата им.

Продукция

SQLite CROSS JOIN Пример

Както можете да видите, резултатът е 40 реда; 10 стойности от таблицата на студентите съответстват на 4-те отдела от таблицата на отделите. Както следва:

  • Четири стойности за четирите отдела от таблицата с отдели съвпадат с първия ученик Мишел.
  • Четири стойности за четирите отдела от таблицата с отдели съвпаднаха с втория ученик Джон.
  • Четири стойности за четирите отдела от таблицата с отдели съвпадат с третия ученик Джак… и така нататък.

Oбобщение

Използването на SQLite JOIN заявка, можете да свържете една или повече таблици или подзаявки заедно, за да изберете колони от двете таблици или подзаявки.