SQLite З’єднання: природний лівий зовнішній, внутрішній, хрест із прикладом таблиць
SQLite підтримує різні типи SQL Об’єднання, наприклад INNER JOIN, LEFT OUTER JOIN і CROSS JOIN. Кожен тип JOIN використовується для різних ситуацій, як ми побачимо в цьому підручнику.
Введення в SQLite Речення JOIN
Коли ви працюєте з базою даних із кількома таблицями, вам часто потрібно отримати дані з цих кількох таблиць.
За допомогою речення JOIN ви можете зв’язати дві або більше таблиць або підзапитів, об’єднавши їх. Також ви можете визначити, за якою колонкою потрібно зв’язати таблиці та за якими умовами.
Будь-яке речення JOIN повинно мати такий синтаксис:

Кожне речення приєднання містить:
- Таблиця або підзапит, який є лівою таблицею; таблицю або підзапит перед реченням об’єднання (зліва від нього).
- Оператор JOIN – вкажіть тип об’єднання (або INNER JOIN, LEFT OUTER JOIN або CROSS JOIN).
- Обмеження JOIN – після того, як ви вказали таблиці або підзапити для об’єднання, вам потрібно вказати обмеження об’єднання, яке буде умовою, за якої відповідні рядки, що відповідають цій умові, будуть вибрані залежно від типу об’єднання.
Зауважте, що для всіх наступних SQLite Приклади таблиць JOIN, вам потрібно запустити sqlite3.exe і відкрити підключення до зразка бази даних як текуче:
Крок 1) На цьому етапі
- Відкрийте «Мій комп’ютер» і перейдіть до наступного каталогу «C:\sqlite"І
- Потім відкрийте "sqlite3.exe":
Крок 2) Відкрийте базу даних "ПідручникиSampleDB.db” за такою командою:
Тепер ви готові виконувати будь-який тип запиту до бази даних.
SQLite INNER JOIN
INNER JOIN повертає лише ті рядки, які відповідають умові об’єднання, і усуває всі інші рядки, які не відповідають умові об’єднання.

Приклад
У наступному прикладі ми об’єднаємо дві таблиці “Студентам"І"відомства” з DepartmentId, щоб отримати назву кафедри для кожного студента, як показано нижче:
SELECT Students.StudentName, Departments.DepartmentName FROM Students INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;
Пояснення коду
INNER JOIN працює так:
- У пропозиції Select ви можете вибрати будь-які стовпці з двох таблиць, на які посилаються.
- Речення INNER JOIN записується після першої таблиці, на яку посилається речення «From».
- Тоді умова приєднання вказується за допомогою ON.
- Для таблиць, на які посилаються, можна вказати псевдоніми.
- Слово INNER необов’язкове, ви можете просто написати JOIN.
Вихід
- INNER JOIN створює записи як для студентів, так і для таблиць кафедри, які відповідають умові, яка є "Students.DepartmentId = Departments.DepartmentId “. Рядки, які не збігаються, будуть проігноровані та не включені до результату.
- Тому лише 8 студентів із 10 студентів були повернуті за цим запитом з ІТ, математики та фізики. Тоді як студенти «Jena» та «George» не були включені, оскільки вони мають нульовий ідентифікатор відділу, який не відповідає стовпцю departmentId у таблиці відділів. Як наступне:
SQLite ПРИЄДНУЙТЕСЯ... ВИКОРИСТАННЯ
INNER JOIN можна написати за допомогою пропозиції «USING», щоб уникнути надмірності, тому замість того, щоб писати «ON Students.DepartmentId = Departments.DepartmentId», ви можете просто написати «USING(DepartmentID)».
Ви можете використовувати «JOIN .. USING» щоразу, коли стовпці, які ви порівнюєте в умові об’єднання, мають однакові назви. У таких випадках немає потреби повторювати їх за допомогою умови on і просто вказати імена стовпців і SQLite виявить це.
Різниця між INNER JOIN і JOIN .. ВИКОРИСТАННЯ:
За допомогою «JOIN … USING» ви не пишете умову об’єднання, ви просто пишете стовпець об’єднання, який є спільним для двох об’єднаних таблиць, замість того, щоб писати table1 «INNER JOIN table2 ON table1.cola = table2.cola», ми пишемо це як «таблиця1 ПРИЄДНАТИСЯ до таблиці2 ВИКОРИСТАННЯ(кола)».
Приклад
У наступному прикладі ми об’єднаємо дві таблиці “Студентам"І"відомства” з DepartmentId, щоб отримати назву кафедри для кожного студента, як показано нижче:
SELECT Students.StudentName, Departments.DepartmentName FROM Students INNER JOIN Departments USING(DepartmentId);
Пояснення
- На відміну від попереднього прикладу, ми не писали «ON Students.DepartmentId = Departments.DepartmentId“. Ми щойно написали "USING(Ідентифікатор відділу)».
- SQLite автоматично визначає умову об’єднання та порівнює DepartmentId з обох таблиць – Students і Departments.
- Ви можете використовувати цей синтаксис щоразу, коли два стовпці, які ви порівнюєте, мають однакові назви.
Вихід
- Це дасть той самий точний результат, що й попередній приклад:
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.
Вихід
- Natural JOIN надасть вам той самий результат, який ми отримали з прикладів INNER JOIN і JOIN USING. Оскільки в нашому прикладі всі три запити еквівалентні. Але в деяких випадках результат буде відрізнятися від внутрішнього об’єднання та природного об’єднання. Наприклад, якщо існує більше таблиць з однаковими іменами, тоді природне об’єднання зіставлятиме всі стовпці один з одним. Однак внутрішнє об’єднання відповідатиме лише стовпцям в умові об’єднання (докладніше в наступному розділі; різниця між внутрішнім об’єднанням і природним об’єднанням).
SQLite ЗАЛИШИЛИ ЗОВНІШНЕ ПРИЄДНАННЯ
Стандарт SQL визначає три типи OUTER JOIN: LEFT, RIGHT і FULL, але 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.
Вихід
- Як ви бачите, включено всі рядки з таблиці студентів, а це всього 10 студентів. Навіть якщо четвертий і останній студент, Jena та George departmentIds не існують у таблиці Departments, вони також включені.
- І в цих випадках значення departmentName як для Jena, так і для George буде «нульовим», оскільки таблиця departments не має departmentName, яке відповідає їхньому значенню departmentId.
Давайте дамо глибше пояснення попередньому запиту з використанням лівого об’єднання за допомогою діаграм Вана:

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» із таблиці departments.
- Для перехресного з’єднання ми не вказали умови з’єднання, лише дві таблиці, об’єднані з CROSS JOIN посередині.
Вихід
Як бачите, виходить 40 рядів; 10 значень із таблиці студентів співпали з 4 відділами з таблиці відділів. Як наступне:
- Чотири значення для чотирьох відділів з таблиці відділів співпали з першим студентом Мішелем.
- Чотири значення для чотирьох відділів із таблиці відділів збігаються з другим студентом Джоном.
- Чотири значення для Чотирьох відділів із таблиці відділів співпали з третім студентом Джеком… і так далі.
Підсумки
використання SQLite У запиті JOIN ви можете зв’язати одну або кілька таблиць або підзапитів, щоб вибрати стовпці з обох таблиць або підзапитів.