SQLite З'єднання: Природне ліве зовнішнє, внутрішнє, перехресне зі столами
Маркус Аллен
⚡ Розумний підсумок
SQLite Речення JOIN об'єднують рядки з двох або більше таблиць за допомогою INNER JOIN, JOIN USING, NATURAL JOIN, LEFT OUTER JOIN та CROSS JOIN, що дозволяє зіставляти пов'язані записи за спільними стовпцями та зчитувати дані з нормалізованої бази даних.
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) Відкрийте базу даних “TutorialsSampleDB.db” за допомогою такої команди:
Тепер ви готові виконувати будь-який тип запиту до бази даних.
SQLite 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.
Вихід
ВНУТРІШНЄ ОБ'ЄДНАННЯ створює записи з таблиць студентів та кафедри, які відповідають умові «Students.DepartmentId = Departments.DepartmentId». Рядки, що не співпадають, будуть ігноруватися та не включатися до результату.
Ось чому з цього запиту було повернуто лише 8 студентів з 10, які мали факультети ІТ, математики та фізики. Тоді як студенти «Джена» та «Джордж» не були включені, оскільки їхній ідентифікатор факультету не відповідає стовпцю departmentId з таблиці departments. Ось як показано нижче:
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», ми пишемо це як «table1 JOIN table2 USING(cola)».
Приклад
У наступному прикладі ми об’єднаємо дві таблиці «Студенти» та «Відділи» за допомогою DepartmentId, щоб отримати назву відділу для кожного студента наступним чином:
SELECT Students.StudentName, Departments.DepartmentName FROM Students INNER JOIN Departments USING(DepartmentId);
Пояснення
- На відміну від попереднього прикладу, ми не писали «ON Students.DepartmentId = Departments.DepartmentId». Ми просто написали «USING(DepartmentId)».
- 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 визначає три типи зовнішніх об'єднань: ліве, праве та повне, але SQLite підтримує лише природне LEFT OUTER JOIN.
У випадку ЛІВОГО ЗОВНІШНЬОГО ОБ'ЄДНАННЯ всі значення стовпців, вибраних з лівої таблиці, будуть включені до результату запиту, тому незалежно від того, чи відповідає значення умові об'єднання чи ні, воно буде включено до результату.
Отже, якщо ліва таблиця має 'n' рядків, результати запиту матимуть 'n' рядків. Однак для значень стовпців, що надходять з правої таблиці, якщо будь-яке значення не відповідає умові об'єднання, воно міститиме значення "null".
Отже, ви отримаєте кількість рядків, еквівалентну кількості рядків у лівому об’єднанні. Таким чином, ви отримаєте відповідні рядки з обох таблиць (наприклад, результати 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.
Вихід
Як бачите, включено всі рядки з таблиці students, що загалом становить 10 студентів. Навіть якщо четвертий та останній студенти, Jena та George, мають departmentId, яких немає в таблиці Departments, вони також включені.
І в цих випадках значення departmentName для Jena та George буде «null», оскільки таблиця departments не має departmentName, яке відповідає їхньому значенню departmentId.
Давайте глибше пояснимо попередній запит з використанням лівого з'єднання за допомогою діаграм Венна:
ЛІВЕ З'ЄДНАННЯ видасть усі імена студентів з таблиці "студенти", навіть якщо студент має ідентифікатор відділу, якого немає в таблиці "відділи". Таким чином, запит не видасть вам лише відповідні рядки як ВНУТРІШНЄ З'ЄДНАННЯ, але й надасть вам додаткову частину, яка містить невідповідні рядки з лівої таблиці, тобто таблиці "студенти".
Зауважте, що будь-яке ім’я студента, яке не має відповідного відділу, матиме «нульове» значення для назви відділу, оскільки для нього немає відповідного значення, і ці значення є значеннями в невідповідних рядках.
SQLite КРОВИЙ ПРИЄДНАЙТЕСЬ
CROSS JOIN дає декартовий добуток для вибраних стовпців двох об’єднаних таблиць шляхом зіставлення всіх значень з першої таблиці з усіма значеннями з другої таблиці.
Отже, для кожного значення в першій таблиці ви отримаєте 'n' збігів з другої таблиці, де n — це кількість рядків другої таблиці.
На відміну від INNER JOIN та LEFT OUTER JOIN, для CROSS JOIN не потрібно вказувати умову об'єднання, оскільки SQLite не потребує цього для перехресного з'єднання.
Команда SQLite призведе до логічного набору результатів шляхом об'єднання всіх значень з першої таблиці з усіма значеннями з другої таблиці.
Наприклад, якщо ви вибрали стовпець з першої таблиці (colA) та інший стовпець з другої таблиці (colB), то стовпець A містить два значення (1,2), а стовпець B також містить два значення (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 відділами з таблиці відділів. Як наступне:
- Чотири значення для чотирьох відділів з таблиці відділів співпали з першим студентом Мішелем.
- Чотири значення для чотирьох кафедр з таблиці кафедр збіглися з другим студентом, Джоном.
- Чотири значення для чотирьох кафедр з таблиці кафедр збіглися з третім студентом Джеком… і так далі.
