Таблици за присъединяване на SQLite: Вътрешен, Естествен, Ляв Външен, Кръстосан (Примери)

Съдържание:

Anonim

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

В този урок ще научите -

  • Въведение в клаузата за присъединяване на SQLite
  • ВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ
  • ПРИСЪЕДИНЕТЕ СЕ ... ИЗПОЛЗВАНЕ
  • ЕСТЕСТВЕНА ПРИСЪЕДИНЕНИЕ
  • ЛЯВО ВЪНШНО ПРИСЪЕДИНЯВАНЕ
  • КРЪСТНО ПРИСЪЕДИНЯВАНЕ

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

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

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

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

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

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

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

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

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

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

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

SQLite INNER JOIN

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

Пример

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

ИЗБЕРЕТЕСтуденти. Име на студент,ДепартаментиОТ студентиВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ НА ОТДЕЛИ ЗА УЧЕНИЦИ.DepartmentId = Отделения.DepartmentId;

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

INNER JOIN работи както следва:

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

Изход:

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

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

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

Можете да използвате „ПРИСЪЕДИНЯВАНЕ ... ИЗПОЛЗВАНЕ“, когато колоните, които ще сравнявате в условието за присъединяване, са едно и също име. В такива случаи няма нужда да ги повтаряте с помощта на условието и просто посочете имената на колоните и SQLite ще открие това.

Разликата между ВЪТРЕШНОТО ПРИСЪЕДИНЯВАНЕ И СЪЕДИНЕНИЕТО ... ИЗПОЛЗВАНЕ

С „ПРИСЪЕДИНЕТЕ СЕ

... ИЗПОЛЗВАЙ "не пишете условие за присъединяване, просто пишете колоната за присъединяване, която е обща между двете съединени таблици, вместо да пишете table1" INNER JOIN table2 ON table1.cola = table2.cola "ние го пишем като" таблица1 ПРИСЪЕДИНЯВАНЕ на таблица2 ИЗПОЛЗВАНЕ (кола) ".

Пример

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

ИЗБЕРЕТЕСтуденти. Име на студент,ДепартаментиОТ студентиВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ НА ОТДЕЛИ ИЗПОЛЗВАНЕ (DepartmentId);

Обяснение

  • За разлика от предишния пример, ние не написахме " ON Students.DepartmentId = Departments.DepartmentId ". Току-що написахме „ USING (DepartmentId) “.
  • SQLite автоматично определя условието за присъединяване и сравнява идентификатора на отдел от двете таблици - студенти и отдели.
  • Можете да използвате този синтаксис, когато двете колони, които сравнявате, са с едно и също име.

Изход

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

SQLite ЕСТЕСТВЕНО СЪЮЗ

ЕСТЕСТВЕНОТО СЪЕДИНЕНИЕ е подобно на ПРИСЪЕДИНЯВАНЕ ... ИЗПОЛЗВАНЕ, разликата е, че той автоматично тества за равенство между стойностите на всяка колона, която съществува в двете таблици.

Разликата между ВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ и ЕСТЕСТВЕНО ПРИСЪЕДИНЯВАНЕ:

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

Пример

ИЗБЕРЕТЕСтуденти. Име на студент,ДепартаментиОТ студентиЕстествени ПРИСЪЕДИНЕТЕ се отдели;

Обяснение

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

Изход

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

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

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

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

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

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

Пример

В следващия пример ще опитаме с „НАЛЯВО ПРИСЪЕДИНЯВАНЕ“, за да се присъединим към двете таблици „Студенти“ и „Катедри“:

ИЗБЕРЕТЕСтуденти. Име на студент,ДепартаментиОТ Студенти - това е лявата таблицаНАЛЯВО СЕ ПРИСЪЕДИНЯВАТ ОТДЕЛИТЕ НА УЧЕНИЦИ.DepartmentId = Departments.DepartmentId;

Обяснение

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

Изход

  • Както можете да видите, са включени всички редове от таблицата за ученици, които са общо 10 ученици. Дори че четвъртият и последният студент, Йена и Джордж отдела за идентификация не съществуват в таблицата на отделите, те също са включени.
  • И в тези случаи стойността departmentName и за Йена, и за Джордж ще бъде "нула", тъй като таблицата на отделите няма отделName, който съответства на стойността им отдел.

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

LEFT JOIN ще даде имена на всички студенти от таблицата на студентите, дори ако студентът има идентификатор на отдел, който не съществува в таблицата на отделите. Така че, заявката няма да ви даде само съответстващите редове като ВЪТРЕШНО ПРИСЪЕДИНЯВАНЕ, но ще ви даде допълнителната част, която има несъответстващи редове от лявата таблица, която е таблицата на студентите.

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

SQLite CROSS JOIN

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 между таблиците за студенти и отдели:

ИЗБЕРЕТЕСтуденти. Име на студент,ДепартаментиОТ студентиКРОСС ПРИСЪЕДИНЕТЕ се отдели;

Обяснение

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

Изход:

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

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

    … и така нататък.

Обобщение

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