Продовжуємо вивчати основи SQL, і настав час поговорити про прості об'єднання JOIN. І сьогодні ми розглянемо, як об'єднуються дані щодо засобів операторів LEFT JOIN, RIGHT JOIN, CROSS JOIN та INNER JOINІншими словами, навчимося писати запити, які об'єднують дані, і як завжди вивчати все це будемо на прикладах.

Об'єднання JOIN дуже важливі в SQL, тому що без уміння писати запити з об'єднанням даних різних об'єктів, просто не обійтися програмісту SQL, та й просто адміну який час від часу вивантажує якісь дані з бази даних, тому це стосується основ SQL і кожен людина, яка має справу з SQL, повинна мати уявлення, що це таке.

Примітка!Всі приклади писатимемо в Management Studio SQL Server 2008.

Ми з Вами вже давно вивчаємо основи SQL, і якщо згадати починали ми з оператора select, і взагалі було вже багато матеріалу на цьому сайті SQL, наприклад:

І багато іншого, навіть уже розглядали об'єднання union і union all, але, так чи інакше, докладніше саме про об'єднання join ми з Вами не розмовляли, тому сьогодні ми заповнимо цю прогалину в наших знаннях.

І почнемо ми як завжди з невеликої теорії.

Об'єднання JOIN— це об'єднання двох або більше об'єктів бази даних за допомогою певного ключа або ключів або у разі cross join і без ключа. Під об'єктами тут мається на увазі різні таблиці, уявлення (views) , табличні функції чи навіть подзапросы sql , тобто. все, що повертає табличні дані.

Об'єднання SQL LEFT та RIGHT JOIN

LEFT JOIN– це об'єднання даних із лівого ключа, тобто. припустимо, ми об'єднуємо дві таблиці по left join, і це означає, що всі дані з другої таблиці підтягнуться до першої, а у разі відсутності ключа виведеться NULL значення, тобто виведуть усі дані з лівої таблиці і всі дані по ключу з правої таблиці.

RIGHT JOIN– це таке ж об'єднання як і Left join тільки виводитимуться всі дані з правої таблиці і тільки ті дані з лівої таблиці в яких є ключ об'єднання.

Тепер давайте розглядати приклади, і для початку створимо дві таблиці:

CREATE TABLE ( (18, 0) NULL, (50) NULL) ON GO CREATE TABLE ( (18, 0) NULL, (50) NULL) ON GO

Ось такі простенькі таблиці, І я для прикладу заповнив їх такими даними:

Тепер давайте напишемо запит із об'єднанням цих таблиць за ключом number, для початку по LEFT:

SELECT t1.number t1_number,t1.text t1_text, t2.number t2_number, t2.text t2_text FROM test_table t1 LEFT JOIN test_table_2 t2 ON t1.number=t2.

Як бачите, тут дані з таблиці t1 вивелися всі, а дані з таблиці t2 не всі, тому що рядки з number = 4 там немає, тому і вивелися значення NULL.

А що буде, якби ми об'єднували за коштами right join, а було б ось це:

SELECT t1.number t1_number,t1.text t1_text, t2.number t2_number, t2.text t2_text FROM test_table t1 RIGHT JOIN test_table_2 t2 ON t1.number=t2.

Іншими словами, вивелися всі рядки з таблиці t2 і відповідні записи з таблиці t1, оскільки всі ключі, які є в таблиці t2, є і в таблиці t1, і тому у нас немає NULL значень.

Об'єднання SQL INNER JOIN

Inner join- це об'єднання коли виводяться всі записи з однієї таблиці і всі відповідні записи з іншої таблиці, а записи яких немає в одній або в іншій таблиці виводитися не будуть, тобто. тільки ті записи, які відповідають ключу. До речі відразу скажу, що inner join це те саме, що і просто join без Inner. Приклад:

SELECT t1.number t1_number,t1.text t1_text, t2.number t2_number, t2.text t2_text FROM test_table t1 INNER JOIN test_table_2 t2 on t1.number=t2.number

А тепер давайте спробуємо об'єднати наші таблиці за двома ключами, для цього трохи згадаємо, як додавати колонку в таблицю і як оновити дані через update, тому що в наших таблицях лише дві колонки, і об'єднувати текстовим полем якось не добре. Для цього додамо колонки:

ALTER TABLE test_table ADD number2 INT ALTER TABLE test_table_2 ADD number2 INT

Обновимо наші дані, просто проставимо в колонку number2 значення 1:

UPDATE test_table SET number2 = 1 UPDATE test_table_2 SET number2 = 1

І давайте напишемо запит із об'єднанням за двома ключами:

SELECT t1.number t1_number,t1.text t1_text, t2.number t2_number, t2.text t2_text FROM test_table t1 INNER JOIN test_table_2 t2 ON t1.number=t2.number=t2.

І результат буде таким самим, як і в попередньому прикладі:

Але якщо ми, скажімо, у другій таблиці в одному рядку змінимо, поле number2 на значення скажемо 2, то результат буде вже зовсім інший.

UPDATE test_table_2 set number2 = 2 WHERE number=1

Запит той самий, а ось результат:

Як бачите, за другим ключем у нас один рядок не вийшов.

Об'єднання SQL CROSS JOIN

CROSS JOIN- це об'єднання SQL за якими кожен рядок однієї таблиці поєднується з кожним рядком іншої таблиці. Особисто у мене це об'єднання рідко потрібне, але все одно іноді потрібно, тому Ви також повинні вміти його використовувати. Наприклад, у нашому випадку вийде, звичайно, не зрозуміло, що, але все одно давайте спробуємо, тим більше синтаксис трохи відрізняється:

SELECT t1.number t1_number,t1.text t1_text, t2.number t2_number, t2.text t2_text FROM test_table t1 CROSS JOIN test_table_2 t2

Тут у кожному рядку таблиці test_table відповідає кожен рядок з таблиці test_table_2, тобто. у таблиці test_table у нас 4 рядки, а у таблиці test_table_2 3 рядки 4 помножити 3 і буде 12, як і у нас вивелося 12 рядків.

І насамкінець, давайте покажу, як можна об'єднувати кілька таблиць, для цього я, просто для прикладу, кілька разів об'єднаю нашу першу таблицю з другою, сенсу в об'єднанні в даному випадку, звичайно, немає, але Ви побачите, як можна це робити і так приступимо:

SELECT t1.number as t1_number, t1.text as t1_text, t2.number as t2_number, t2.text as t2_text, t3.number as t3_number, t3.text as t3_text, t4. ROM test_table t1 LEFT JOIN test_table_2 t2 on t1.number=t2.number RIGHT JOIN test_table_2 t3 on t1.number=t3.number INNER JOIN test_table_2 t4 on t1.number=t4.number

Як бачите, я тут об'єдную і по left і по right і по inner просто, щоб це було наочно.

З об'єднаннями я думаю достатньо, тим паче нічого складного у них немає. Але на цьому вивчення SQL не закінчено в наступних статтях ми продовжимо, а поки що тренуйтеся і пишіть свої запити. Успіхів!

Раніше ми розглянули застосування інструкції SELECT для вибірки даних однієї таблиці бази даних. Якби можливості мови Transact-SQL обмежувалися підтримкою лише таких простих інструкцій SELECT, то приєднання в запиті двох або більше таблиць для вибірки даних було б неможливо. Отже, всі дані бази даних потрібно зберігати в одній таблиці. Хоча такий підхід є цілком можливим, йому притаманний один значний недолік - дані, що зберігаються таким чином, характеризуються високою надмірністю.

Мова Transact-SQL усуває цей недолік, надаючи для цього оператор з'єднання JOIN, що дозволяє витягувати дані з більш ніж однієї таблиці. Цей оператор, напевно, є найважливішим оператором для реляційних систем баз даних, оскільки йому є можливість розподіляти дані з кількох таблицях, забезпечуючи, таким чином, важливе властивість систем баз даних - відсутність надмірності даних.

Оператор UNION, який ми розглянули раніше, також дозволяє виконувати запит на кілька таблиць. Але цей оператор дозволяє приєднати кілька інструкцій SELECT, тоді як оператор з'єднання JOIN з'єднує кілька таблиць з використанням лише однієї інструкції SELECT. Окрім цього, оператор UNION поєднує рядки таблиць, тоді як оператор JOIN з'єднує стовпці.

Оператор з'єднання також можна застосовувати з базовими таблицями та уявленнями. Оператор з'єднання JOIN має декілька різних форм. У цій статті розглядаються такі основні форми цього оператора:

природне з'єднання;

декартове твір або перехресне з'єднання;

зовнішнє з'єднання;

тета-з'єднання, самоз'єднання та напівз'єднання.

Перш ніж розпочати розгляд різних форм з'єднань, у цьому розділі ми розглянемо різні варіантиоператора з'єднання JOIN.

Дві синтаксичні форми реалізації сполук

Для з'єднання таблиць можна використовувати дві різні синтаксичні форми оператора з'єднання:

явний синтаксис з'єднання (синтаксис з'єднання ANSI SQL: 1992);

неявний синтаксис з'єднання (синтаксис "старого стилю").

Синтаксис з'єднання ANSI SQL:1992 був запроваджений стандартом SQL92 і визначає операції з'єднання явно, тобто. використовуючи відповідне ім'я для кожного типу операції з'єднання. При явному оголошенні з'єднання використовуються такі ключові слова:

LEFT JOIN;

RIGHT JOIN;

FULL JOIN.

Ключове слово CROSS JOIN визначає декартове твір двох таблиць. Ключове слово INNER JOIN визначає природне з'єднання двох таблиць, а LEFT OUTER JOIN та RIGHT OUTER JOIN визначають однойменні операції з'єднання. Зрештою, ключове слово FULL OUTER JOIN визначає з'єднання правого та лівого зовнішнього з'єднань. Всі ці операції з'єднання розглядаються у наступних розділах.

Неявний синтаксис оператора з'єднання є синтаксисом "старого стилю", де кожна операція з'єднання визначається неявно шляхом пропозиції WHERE, використовуючи так звані стовпці з'єднання.

Для операцій з'єднання рекомендується використовувати синтаксис, т.к. це підвищує надійність запитів. З цієї причини у всіх прикладах, пов'язаних з операціями сполуки, використовуються форми явного синтаксису. Але в перших прикладах також буде продемонстрований і синтаксис "старого стилю".

Природне поєднання

Терміни "природне з'єднання" (natural join) та "з'єднання за еквівалентністю" (equi-join) часто використовують синонімічно, але між ними є невелика відмінність. Операція з'єднання еквівалентності завжди має одну або кілька пар стовпців з ідентичними значеннями в кожному рядку. Операція, яка усуває такі стовпці з результатів операції сполуки за еквівалентністю, називається природною сполукою. Найкращим способомпояснити природне з'єднання можна за допомогою прикладу:

USE SampleDb; SELECT Employee.*, Department.* FROM Employee INNER JOIN Department ON Employee.DepartamentNumber = Department.Number;

Запит повертає всю інформацію про всіх співробітників: ім'я та прізвище, табельний номер, а також ім'я, номер та місцезнаходження відділу, для номера відділу відображаються дублікати стовпців з різних таблиць.

У цьому прикладі в інструкції SELECT для вибірки вказані всі стовпці таблиць для працівника Employee та відділу Department. Пропозиція FROM інструкції SELECT визначає таблиці, що з'єднуються, а також явно вказує тип операції з'єднання - INNER JOIN. Пропозиція ON є частиною пропозиції FROM і вказує стовпці, що з'єднуються в обох таблицях. Вираз "Employee.DepartamentNumber = Department.Number" визначає умову з'єднання, а обидва стовпці умови називаються стовпцями з'єднання.

Еквівалентний запит із застосуванням неявного синтаксису ("старого стилю") виглядатиме так:

Ця форма синтаксису має дві значні відмінності з явною формою: список таблиць, що з'єднуються, вказується в пропозиції FROM, а відповідна умова з'єднання вказується в пропозиції WHERE за допомогою стовпців, що з'єднуються.

На попередніх прикладах можна проілюструвати принцип роботи операції з'єднання. Але при цьому слід мати на увазі, що це лише уявлення про процес з'єднання, т.к. Насправді компонент Database Engine вибирає реалізацію операції з'єднання з кількох можливих стратегій. Уявіть, що кожен рядок таблиці Employee з'єднаний з кожним рядком таблиці Department. В результаті вийде таблиця з сімома стовпцями (4 стовпці з таблиці Employee і 3 з таблиці Department) та 21 рядком.

Далі з цієї таблиці видаляються всі рядки, які не задовольняють умову з'єднання "Employee.Number = Department.Number". Рядки, що залишилися, представляють результат першого прикладу вище. Сполучні стовпці повинні мати ідентичну семантику, тобто. обидва стовпці повинні мати однакове логічне значення. Стовпці, що з'єднуються, не обов'язково повинні мати однакове ім'я (або навіть однаковий тип даних), хоча часто так і буває.

Система бази даних неспроможна визначити логічне значення шпальти. Наприклад, вона не може визначити, що між стовпцями номера проекту та табельного номера співробітника немає нічого спільного, хоча обидва вони мають цілий тип даних. Тому система бази даних може лише перевірити тип даних та довжину рядків. Компонент Database Engine вимагає, що стовпці, що з'єднуються, мали сумісні типи даних, наприклад INT і SMALLINT.

База даних SampleDb містить три пари стовпців, де кожен стовпець у парі має однакове логічне значення (також однакові імена). Таблиці Employee та Department можна з'єднати по стовпцях Employee.DepartmentNumber та Department.Number. Стовпцями з'єднання таблиць Employee та Works_on є стовпці Employee.Id та Works_on.EmpId. Нарешті, таблиці Project та Works_on можна з'єднати по стовпцях Project.Number та Works_on.ProjectNumber.

Імена стовпців можна знайти в інструкції SELECT. У цьому контексті під уточненням мається на увазі, що, щоб уникнути невизначеності щодо того, якій таблиці належить стовпець, в ім'я стовпця включається ім'я його таблиці (або псевдонім таблиці), відокремлене точкою:

table_name.column_name (ім'я_таблиці.ім'я_стовпця)

У більшості інструкцій SELECT стовпці не вимагають уточнення, хоча зазвичай рекомендується застосовувати уточнення стовпців з метою покращення розуміння коду. Якщо імена стовпців в інструкції SELECT неоднозначні (як, наприклад, стовпці Number у таблицях Project та Department) використання уточнених імен стовпців є обов'язковим.

В інструкції SELECT з операцією з'єднання, крім умови з'єднання пропозиція WHERE може містити інші умови, як це показано в прикладі нижче:

USE SampleDb; -- Явний синтаксис SELECT EmpId, Project.Number, Job, EnterDate, ProjectName, Budget FROM Works_on JOIN Project ON Project.Number = Works_on.ProjectNumber WHERE ProjectName = "Gemini"; -- Старий стиль SELECT EmpId, Project.Number, Job, EnterDate, ProjectName, Budget FROM Works_on, Project WHERE Project.Number = Works_on.ProjectNumber AND ProjectName = "Gemini";

Використання уточненого імені стовпця Project.Number у прикладі вище не є обов'язковим, оскільки в цьому випадку немає жодної двозначності щодо їхніх імен. Надалі у всіх прикладах використовуватиметься лише явний синтаксис сполуки.

У прикладі нижче показано ще одне застосування внутрішньої сполуки:

З'єднання більш ніж двох таблиць

Теоретично кількість таблиць, які можна з'єднати в інструкції SELECT, необмежена. (Але одна умова з'єднання поєднує лише дві таблиці!) Однак для компонента Database Engine кількість таблиць, що з'єднуються, в інструкції SELECT обмежена 64 таблицями.

У прикладі нижче показано з'єднання трьох таблиць бази даних SampleDb:

USE SampleDb; - Поверне єдиного співробітника "Василь Фролов" SELECT FirstName, LastName FROM Works_on JOIN Employee ON Works_on.EmpId=Employee.Id JOIN Department ON Employee.DepartamentNumber=Department.Number AND Location="

У цьому вся прикладі відбувається вибірка імен і прізвищ всіх аналітиків (Job = " Аналітик " ), чий відділ перебуває у Санкт-Петербурзі (Location = " Санкт-Петербург " ). Результат запиту, наведеного в прикладі вище, можна отримати тільки в тому випадку, якщо з'єднати принаймні три таблиці: Works_on, Employee та Department. Ці таблиці можна з'єднати, використовуючи дві пари стовпців з'єднання:

(Works_on.EmpId, Employee.Id) (Employee.DepartmentNumber, Department.Number)

Зверніть увагу, що для здійснення природного з'єднання трьох таблиць використовуються дві умови з'єднання, кожна з яких з'єднує дві таблиці. При з'єднанні чотирьох таблиць таких умов з'єднання потрібно три. Загалом, щоб уникнути отримання декартового продукту при з'єднанні таблиць n, потрібно застосовувати n - 1 умов з'єднання. Звичайно ж, допустиме використання більш ніж n - 1 умов з'єднання, а також інших умов для того, щоб ще більше зменшити кількість елементів в результуючому наборі даних.

Декартове твір

У попередньому розділі ми розглянули можливий спосіб створення природних сполук. На першому етапі цього процесу кожен рядок таблиці Employee з'єднується з кожним рядком таблиці Department. Ця операція називається декартовим твором (cartesian product). Запит для створення з'єднання таблиць Employee та Department, використовуючи декартовий твір, показаний у прикладі нижче:

USE SampleDb; SELECT Employee.*, Department.* FROM Employee CROSS JOIN Department;

Декартове твір з'єднує кожен рядок першої таблиці з кожним рядком другий. Загалом, результатом декартового добутку першої таблиці з n рядками та другої таблиці з m рядками буде таблиця з n*m рядками. Таким чином, результуючий набір запиту в прикладі вище має 7х3 = 21 рядок (ці рядки містять дубльовані значення).

Насправді декартово твір застосовується вкрай рідко. Іноді користувачі отримують декартове добуток двох таблиць, коли вони забувають включити умову з'єднання в пропозиції WHERE при використанні неявного синтаксису "старого стилю". У разі отриманий результат відповідає очікуваному, т.к. містить зайві рядки. Наявність несподівано великої кількості рядків у результаті є ознакою того, що замість необхідного природного з'єднання двох таблиць було отримано декартове твір.

Зовнішнє з'єднання

У попередніх прикладах природного з'єднання, результуючий набір містив ті рядки з однієї таблиці, для яких були відповідні рядки в іншій таблиці. Але іноді крім збігаються рядків буває необхідним витягти з однієї або обох таблиць рядки без збігів. Така операція називається зовнішнім з'єднанням (outer join).

У прикладі нижче показано вибірку всієї інформації для співробітників, які проживають і працюють в тому самому місті. Тут використовується таблиця EmployeeEnh, яку ми створили у статті "Інструкція SELECT: розширені можливості" під час обговорення оператора UNION.

USE SampleDb; SELECT DISTINCT EmployeeEnh.*, Department.Location FROM EmployeeEnh JOIN Department ON City = Location;

Результат виконання цього запиту:

У цьому прикладі отримання необхідних рядків здійснюється за допомогою природного з'єднання. Якби цей результат потрібно було включити співробітників, які у інших місцях, потрібно було застосувати ліве зовнішнє з'єднання. Дане зовнішнє з'єднання називається лівим тому, що воно повертає всі рядки з таблиці з лівого боку оператора порівняння, незалежно від того, чи є рядки, що збігаються в таблиці з правої сторони. Іншими словами, дане зовнішнє з'єднання поверне рядок з лівої таблиці, навіть якщо для неї немає збігу в правій таблиці, зі значенням NULL відповідного стовпця для всіх рядків з незмінним значенням стовпця іншої, правої таблиці. Для виконання операції лівого зовнішнього з'єднання компонент Database Engine використовує оператор LEFT OUTER JOIN.

Операція правого зовнішнього з'єднання аналогічна лівому, але повертаються усі рядки таблиці з правої частини виразу. Для виконання операції правого зовнішнього з'єднання компонент Database Engine використовує оператор RIGHT OUTER JOIN.

USE SampleDb; SELECT EmployeeEnh.*, Department.Location FROM EmployeeEnh LEFT OUTER JOIN Department ON City = Location;

У цьому прикладі відбувається вибірка співробітників (з включенням повної інформації) для таких міст, в яких співробітники або лише проживають (стовпець City в таблиці EmployeeEnh), або проживають та працюють. Результат виконання цього запиту:

Як можна бачити в результаті виконання запиту, коли для рядка з лівої таблиці (в даному випадку EmployeeEnh) немає рядка, що збігається в правій таблиці (в даному випадку Department), операція лівого зовнішнього з'єднання все одно повертає цей рядок, заповнюючи значенням NULL всі комірки відповідного стовпця для незбігаючого значення стовпця правої таблиці. Застосування правої зовнішньої сполуки показано на прикладі нижче:

USE SampleDb; SELECT EmployeeEnh.City, Department.* FROM EmployeeEnh RIGHT OUTER JOIN Department ON City = Location;

У цьому прикладі відбувається вибірка відділів (з включенням повної інформації про них) для таких міст, де співробітники або тільки працюють, або проживають і працюють. Результат виконання цього запиту:

Крім лівого та правого зовнішнього з'єднання, також існує повне зовнішнє з'єднання, яке є об'єднанням лівого та правого зовнішніх з'єднань. Іншими словами, результуючий набір такого з'єднання складається зі всіх рядків обох таблиць. Якщо для рядка однієї з таблиць немає відповідного рядка в іншій таблиці, всім осередкам рядка другої таблиці надається значення NULL. Для виконання операції повного зовнішнього з'єднання використовується оператор FULL OUTER JOIN.

Будь-яку операцію зовнішнього з'єднання можна емулювати, використовуючи оператор UNION разом із функцією NOT EXISTS. Таким чином, запит, показаний у прикладі нижче, еквівалентний запиту лівого зовнішнього з'єднання, показаному раніше. У цьому запиті здійснюється вибірка співробітників (з включенням повної інформації) для таких міст, в яких співробітники або лише проживають чи проживають та працюють:

Перша інструкція SELECT об'єднання визначає природне з'єднання таблиць EmployeeEnh та Department по стовпцях з'єднання City та Location. Ця інструкція повертає всі міста для всіх співробітників, в яких працівники проживають і працюють. Додатково друга інструкція SELECT об'єднання повертає всі рядки таблиці EmployeeEnh, які не відповідають умові в природному з'єднанні.

Інші форми операцій з'єднання

У попередніх розділах ми розглянули найважливіші форми з'єднання. Але існують інші форми цієї операції, які ми розглянемо в наступних підрозділах.

Тета-з'єднання

Умова порівняння стовпців з'єднання не обов'язково має бути рівністю, але може бути будь-яким порівнянням. З'єднання, в якому використовується загальна умова порівняння стовпців з'єднання, називається тета-з'єднанням. У прикладі нижче показана операція тета-з'єднання, в якій використовується умова "менше ніж". Цей запит повертає всі комбінації інформації про співробітників та відділи для тих випадків, коли місце проживання співробітника за алфавітом йде перед місцем розташування будь-якого відділу, в якому працює цей службовець:

USE SampleDb; SELECT FirstName, LastName, City, Location FROM EmployeeEnh JOIN Department ON City

Результат виконання цього запиту:

У цьому прикладі порівнюються відповідні значення стовпців City та Location. У кожному рядку результату значення стовпця City порівнюється в алфавітному порядку з відповідним значенням стовпця Location.

Самоз'єднання, або з'єднання таблиці самої із собою

Крім з'єднання двох або більше різних таблиць, операцію природного з'єднання можна застосувати до однієї таблиці. У цій операції таблиця з'єднується сама з собою, причому один стовпець таблиці порівнюється сам із собою. Порівнювання стовпця із собою означає, що у пропозиції FROM інструкції SELECT ім'я таблиці використовується двічі. Тому необхідно мати можливість посилатися на ім'я однієї і тієї ж таблиці двічі. Це можна здійснити, використовуючи принаймні один псевдонім. Те саме стосується і імен стовпців за умови з'єднання в інструкції SELECT. Щоб розрізнити стовпці з однаковими іменами, необхідно використовувати уточнені імена.

З'єднання таблиці з собою демонструється в прикладі нижче:

У цьому прикладі відбувається вибірка всіх відділів (з повною інформацією), розташованих у тому самому місці, як і, принаймні, один інший відділ. Результат виконання цього запиту:

Тут пропозиція FROM містить два псевдоніми для таблиці Department: t1 і t2. Першу умову в пропозиції WHERE визначать стовпці з'єднання, а друга видаляє непотрібні дублікати, забезпечуючи порівняння кожного відділу з іншими відділами.

Напівз'єднання

Напівз'єднання схоже на природне з'єднання, але повертає лише набір всіх рядків з однієї таблиці, для якої в іншій таблиці є один або кілька збігів. Використання напівз'єднання показано у прикладі нижче:

Результат виконання запиту:

Як можна побачити, список вибору SELECT у напівз'єднанні містить лише стовпці з таблиці Employee. Це і є характерною рисою операції напівз'єднання. Ця операція зазвичай застосовується в розподіленій обробці запитів, щоб звести до мінімуму обсяг даних, що передаються. Компонент Database Engine використовує операцію напівз'єднання для реалізації функціональності, що називається з'єднанням типу "зірка".

Розробка будь-якої бази даних передбачає не лише створення та наповнення таблиць різноманітною інформацією, а й подальшу роботу з даними. Для коректного виконання різноманітних завдань щодо вибору даних із таблиць та формування звітів, використовується стандартна конструкція Select.

Вибірки даних із таблиць

Якщо розглядати завдання вибору даних чи побудови деякого звіту, можна визначити рівень складності цієї операції. Як правило, при роботі з серйозними (за обсягом інформації) базами даних, які формуються, наприклад, в інтернет-магазинах або великих компаніях, вибірка даних не обмежуватиметься лише однією таблицею. Як правило, вибірки можуть бути з досить великої кількості не тільки пов'язаних між собою таблиць, а й вкладених запитів/підзапитів, які складає сам програміст, залежно від поставленого перед ним завдання. Для вибірки з однієї таблиці можна використовувати найпростішу конструкцію:

Select * from Person

де Person – ім'я таблиці, з якої необхідно зробити вибірку даних.

Якщо буде необхідність вибрати дані з кількох таблиць, можна використовувати одну зі стандартних конструкцій для об'єднання кількох таблиць.

Способи підключення додаткових таблиць

Якщо розглядати використання такого роду конструкцій на початковому рівні, то можна виділити такі механізми підключення необхідної кількості таблиць для вибірки, а саме:

Оператор Inner Join.
Left Join або, це другий спосіб запису, Left Outer Join.
Cross Join.
Full Join.

Використання операторів об'єднання таблиць на практиці можна засвоїти, розглянувши застосування оператора SQL – Inner Join. Приклад його використання буде виглядати так:

Select * from Person

Мова SQL та оператор Join Inner Join можна використовувати не тільки для об'єднання двох і більше таблиць, але і для підключення інших підзапитів, що значно полегшує роботу адміністраторів бази даних і, як правило, може значно прискорити виконання певних складних за структурою запитів.

Об'єднання даних у таблицях рядково

Якщо розглядати підключення великої кількості підзапитів та складання даних у єдину таблицю рядок за рядком, можна використовувати також оператори Union, і Union All.

Застосування цих конструкцій залежатиме від поставленої перед розробником завдання та результату, якого він хоче досягти у результаті.

Опис оператора Inner Join

Найчастіше для об'єднання кількох таблиць у мові SQL використовується оператор Inner Join. Опис Inner Join у SQL досить просте розуміння середньостатистичного програміста, який тільки починає розбиратися в базах даних. Якщо розглянути опис механізму роботи цієї конструкції, отримаємо наступну картину. Логіка оператора в цілому побудована на можливості перетину та вибірки тільки тих даних, які є в кожній із таблиць, що входять у запит.

Якщо розглянути таку роботу з погляду графічної інтерпретації, отримаємо структуру оператора SQL Inner Join, приклад якої можна показати за допомогою наступної схеми:

Наприклад, маємо дві таблиці, схема яких показано малюнку. Вони, у свою чергу, мають різну кількість записів. У кожній із таблиць є поля, які пов'язані між собою. Якщо спробувати пояснити роботу оператора виходячи з малюнка, то результат, що повертається буде у вигляді набору записів з двох таблиць, де номери пов'язаних між собою полів збігаються. Простіше кажучи, запит поверне ті записи (з таблиці номер два), дані про які є в таблиці номер один.

Синтаксис оператора Inner Join

Як уже говорилося раніше, оператор Inner Join, а саме його синтаксис, надзвичайно простий. Для організації зв'язків між таблицями в межах однієї вибірки достатньо буде запам'ятати та використовувати наступну принципову схему побудови оператора, яка прописується в один рядок програмного SQL-коду, а саме:

Inner Join [Ім'я таблиці] on [ключове поле з таблиці, до якої підключаємо] = [Ключового поля таблиці, що підключається].

Для зв'язку в даному операторіВикористовуються основні ключі таблиць. Як правило, у групі таблиць, які зберігають інформацію про співробітників, раніше описані Person та Subdivision мають хоча б по одному схожому запису. Отже, розглянемо докладніше Inner Join, приклад якого було показано раніше.

Приклад та опис підключення до вибірки однієї таблиці

У нас є таблиця Person, де зберігається інформація про всіх співробітників, які працюють у компанії. Відразу зазначимо, що основним ключем даної таблиці є поле - Pe_ID. Саме по ньому і йтиме зв'язка.

Друга таблиця Subdivision зберігатиме інформацію про підрозділи, у яких працюють співробітники. Вона, своєю чергою, пов'язані з допомогою поля Su_Person з таблицею Person. Про що це каже? З схеми даних можна сказати, що у таблиці підрозділів кожної записи з таблиці «Співробітники» буде інформація про відділ, де вони працюють. Саме через це і працюватиме оператор Inner Join.

Для більш зрозумілого використання розглянемо оператор SQL Inner Join (приклади використання для однієї і двох таблиць). Якщо розглядати приклад однієї таблиці, то тут все досить просто:

Select * from Person

Inner join Subdivision на Su_Person = Pe_ID

Приклад підключення двох таблиць та підзапиту

Оператор SQL Inner Join, приклади використання якого для вибірки даних з декількох таблиць можна організувати вищезазначеним чином, працює за трохи ускладненим принципом. Для двох таблиць ускладнимо завдання. Скажімо, у нас є таблиця Depart, де зберігається інформація про всі відділи в кожному з підрозділів. У цій таблиці записаний номер підрозділу і номер співробітника і потрібно доповнити вибірку даних назвою кожного відділу. Забігаючи наперед, варто сказати, що для вирішення цього завдання можна скористатися двома методами.

Перший спосіб полягає у підключенні таблиці відділів до вибірки. Організувати запит у цьому випадку можна таким чином:

Така конструкція не завжди може прискорити роботу запиту. Іноді бувають випадки, коли доводиться використовувати додаткову вибірку даних у тимчасову таблицю (якщо їх обсяг занадто великий), а потім її об'єднувати з основною вибіркою.

Приклад використання оператора Inner Join для вибірок із великої кількості таблиць

Побудова складних запитів передбачає використання для вибірки даних значної кількості таблиць та підзапитів, пов'язаних між собою. Цим вимогам може задовольнити синтаксис SQL Inner Join. Приклади використання оператора у разі можуть ускладнюватися як вибірками з багатьох місць зберігання даних, а й із великої кількості вкладених підзапитів. Для конкретного прикладу можна взяти вибірку даних із системних таблиць (оператор Inner Join SQL). Приклад - 3 таблиці - у разі матиме досить складну структуру.

У цьому випадку підключено (до основної таблиці) ще три додатково та введено декілька умов вибору даних.

При використанні оператора Inner Join варто пам'ятати про те, що чим складніше запит, тим довше він реалізовуватиметься, тому варто шукати шляхи швидшого виконання та вирішення поставленого завдання.

Висновок

У підсумку хотілося б сказати одне: робота з базами даних - це не найскладніше, що є в програмуванні, тому за бажання абсолютно кожна людина зможе оволодіти знаннями щодо побудови баз даних, а згодом, набравшись досвіду, вдасться працювати з ними на професійному рівні. .

Оператор JOINвикористовується для виконання операції з'єднання даних з двох наборів один результуючий набір. Може бути використано декілька різних типівоперацій з'єднання під час виконання SELECT . Які рядки увійдуть до результуючого набору залежить від типу операції з'єднання і від явно певної умовиз'єднання. Умова сполуки, тобто. Умова зіставлення рядків таблиць один з одним, є логічним виразом.

При необхідності поєднання кількох таблиць операція з'єднання повинна застосовуватися послідовно кілька разів.

Синтаксис оператора JOIN

Синтаксис підключення до запиту додаткової таблиці за допомогою оператора JOINможна уявити в наступному вигляді:

У більшості СУБД під час використання оператора JOINу поєднанні з ключовими словами LEFT, RIGHT, FULL можна опустити операнд OUTER. Операнд INNER також у більшості СУБД не можна використовувати.

Якщо назви стовпців, якими відбувається з'єднання таблиць, збігаються, то замість ON можна використовувати USING. Для оператора CROSS JOINумова не вказується.

Тестові таблиці для перевірки JOIN

Для подальших прикладів створимо дві прості тестові таблиці. Довідкові таблиці користувачів та їх автомобілів. У першій таблиці зберігатиметься ідентифікатор користувача та його ім'я (nick). У другій таблиці список автомобілів (ідентифікатор, найменування) та ідентифікатор їх власників/користувачів. Необхідно відзначити, що користувач може мати декілька автомобілів або взагалі не мати.

Таблиця користувачів

CREATE TABLE users (id int not null, name varchar(32) not null, primary key (id)); -- Вставимо в таблицю кілька записів insert inusers (id, name) values (1, "alex"); insert в users (id, name) values (2, " piter " ); insert в users (id, name) values (3, " serg " ); insert в users (id, name) values (4, " olga " ); insert в users (id, name) values (5, "ostap");

Таблиця автомобілів

CREATE table autos (id int not null, - ідентифікатор авто name varchar(32) not null, - найменування авто oid int not null, - ідентифікатор власника primary key (id)); -- Вставимо в таблицю кілька записів insert в autos (id, oid, name) values (1, 1, "toyota camry"); insert в autos (id, oid, name) values (2, 1, " Toyota prado " ); insert в autos (id, oid, name) values (3, 2, "renault megane"); insert в autos (id, oid, name) values (4, 3, " nissan x-trail " ); insert в autos (id, oid, name) values (5, 4, " suzuki swift " ); insert в autos (id, oid, name) values (6, 4, " suzuki vitara " );

Внутрішнє з'єднання, INNER JOIN

INNER JOIN- Це оператор внутрішнього з'єднання двох таблиць. Він симетричний, тому порядок таблиць для оператора неважливий.

Застосовується INNER JOINдля отримання лише тих рядків, для яких існує відповідність записів у головній та таблиці, що приєднується. Алгоритм формування результату: кожен рядок головної таблиці зіставляється з кожним рядком таблиці, що приєднується. Після цього перевіряється умова з'єднання. Якщо умова істинна, до результуючого набору додається відповідний «з'єднаний» рядок.

Запит з вибором колонок результуючого набору select u.name as owner, a.name як auto з users u inner join autos a ON a.oid = u.id -- Результат запиту owner auto alex toyota camry alex toyota prado piter x-trail olga suzuki swift olga suzuki vitara -- Запит без вибору колонок результуючого набору select * from users u inner join autos on a.oid = u.id -- Результат запиту id name1 oid 1 alex 1 toyota camry 1 1 alex 2 toyota prado 1 2 piter 3 renault megane 2 3 serg 4 nissan x-trail 3 4 olga 5 suzuki swift 4 4 olga 6 suzuki vitara 4

Щоб отримати дані, які не підходять за умовою, необхідно використати зовнішнє об'єднання - OUTER JOIN.

Зовнішнє об'єднання, OUTER JOIN

При з'єднанні двох таблиць оператором OUTER JOINв результуючий набір обов'язково увійдуть рядки або однієї з таблиць, або обох таблиць. Ключове слово OUTER можна опустити. Запис LEFT JOIN ідентичний LEFT OUTER JOIN.

Існує два типи зовнішнього об'єднання. Це LEFT OUTER JOINі RIGHT OUTER JOIN. Працюють дані оператори однаково. Різниця полягає в тому, що при використанні LEFT JOIN основною таблицею є таблиця, яка вказана після оператора FROM. До рядків даної таблиці за заданих умов додаються дані таблиці, що приєднується. Для оператора RIGHT OUTER JOIN все з точністю навпаки.

Оператор зовнішнього з'єднання OUTER JOINне є симетричним, тому порядок встановлення зв'язку між таблицями для оператора є важливим.

Приклад використання оператора LEFT OUTER JOIN

Виберіть u.name як власника, a.name як авто від users u left join autos ON a.oid = u.id -- Результат запиту owner auto alex toyota camry alex toyota prado piter olga suzuki vitara ostap

Графічно результат роботи можна представити так:

Оператор LEFT OUTER JOIN із фільтрацією

Додавши до коду попереднього прикладу умову "where a.name is null". У вибірці залишиться лише один запис "ostap", тому що тільки у нього не визначено автомобіль.

Графічно результат роботи можна представити так:

Оператор перехресного з'єднання, CROSS JOIN

CROSS JOIN - це оператор перехресного з'єднання (декартове твір). Оператор є симетричним і порядок таблиць оператора неважливий.

Результатуючий набір формується наступним чином: кожен рядок однієї таблиці з'єднується з кожним рядком другої таблиці, даючи цим в результаті всі можливі поєднання рядків двох таблиць. Для нашого прикладу це виходить 30 рядків результуючого набору.

Виберіть * від users cross join autos -- Результат запиту id name id1 name1 oid 1 alex 1 toyota camry 1 2 piter 1 toyota camry 1 3 serg 1 toyota camry 1 4 olga 1 toyota camry 1 5 ostap 1 toyota camry 1 prado 1 2 piter 2 toyota prado 1 3 serg 2 toyota prado 1 4 olga 2 toyota prado 1 5 ostap 2 toyota prado 1 . . .

У результатах набору наведено лише перші 12 рядків.

Це навчальний посібник Oracle пояснює, як використовувати JOINS (INNER та OUTER)в Oracle з синтаксисом та прикладами.

Опис

Oracle JOINSвикористовуються для отримання даних з декількох таблиць. JOINвиконується щоразу, коли дві чи більше таблиць об'єднуються в SQL реченні.

Є 4 різних типуприєднання Oracle:

Розглянемо синтаксис Oracle JOIN, а також вивчимо приклади Oracle JOIN.

INNER JOIN (просте з'єднання)

Швидше за все, ви вже писали запити, в яких використовуються Oracle INNER JOIN. Це найпоширеніший тип з'єднання. Oracle INNER JOINS повертає всі рядки з кількох таблиць, де виконуються умови з'єднання.

Синтаксис

Синтаксис INNER JOIN у Oracle/PLSQL:

SELECT columns
FROM table1
INNER JOIN table2

У цьому малюнку Oracle INNER JOIN повертає затінену область:

Oracle INNER JOIN повертатиме записи, де table1 та table2 будуть перетинатися.

приклад

Нижче наведено приклад Oracle INNER JOIN:

Oracle PL/SQL

SELECT suppliers.supplier_id,

suppliers.supplier_name,

orders.order_date

FROM suppliers

INNER JOIN orders

Цей приклад Oracle INNER JOIN повертає всі рядки таблиць suppliers і orders , де є відповідні значення поля supplier_id в обох таблицях.

Розглянемо деякі дані, щоб зрозуміти, як працює INNER JOIN:

Якщо ми виконаємо Oracle оператор SELECT (який містить INNER JOIN):

Oracle PL/SQL

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

Рядки для Microsoft і NVIDIA з таблиці suppliers будуть опущені, оскільки значення supplier_id 10002 та 10003 не існує в обох таблицях. Рядок order_id 500127 з таблиці orders буде опущений, оскільки supplier_id 10004 не існує в таблиці suppliers .

Старий синтаксис

Як останнє примітки, варто зазначити, що наведений вище приклад Oracle INNER JOIN можна переписати, використовуючи старий неявний синтаксис наступним чином (але рекомендується використовувати синтаксис INNER JOIN):

Інший тип з'єднання називається Oracle LEFT OUTER JOIN. Цей тип з'єднання повертає всі рядки з таблиць з лівим з'єднанням, зазначеним в умові ON, і тільки ті рядки з іншої таблиці, де поля, що об'єднуються, рівні.

Синтаксис

Синтаксис для Oracle LEFT OUTER JOIN:

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

У деяких базах даних LEFT OUTER JOIN замінюється LEFT JOIN.

На цьому малюнку Oracle LEFT OUTER JOIN повертає затінену область:

Oracle LEFT OUTER JOIN поверне всі записи з table1 і тільки записи з table2, які перетинаються з table1.

приклад

Oracle PL/SQL

SELECT suppliers.supplier_id,

suppliers.supplier_name,

orders.order_date

FROM suppliers

LEFT OUTER JOIN orders

ON suppliers.supplier_id=orders.supplier_id;

Цей приклад LEFT OUTER JOIN поверне всі рядки з таблиці suppliers , і ті рядки з таблиці orders , де поля, що об'єднуються рівні.

Якщо значення supplier_id у таблиці suppliers не існує в таблиці orders , усі поля таблиці orders будуть відображатися в результуючому наборі як NULL.

Розглянемо деякі дані, щоб зрозуміти, як працює LEFT OUTER JOIN:

У нас є таблиця suppliers з двома полями (supplier_id та supplier_name ) яка містить такі дані:

Якщо ми виконаємо Oracle оператор SELECT (який містить LEFT OUTER JOIN):

Oracle PL/SQL

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

Рядки для Microsoft і NVIDIA будуть включені, оскільки використали LEFT OUTER JOIN. Тим не менш, ви помітите, що поле order_date для цих записів містить NULL.

Інший тип з'єднання називається Oracle RIGHT OUTER JOIN. Цей тип з'єднання повертає всі рядки з таблиць з правостороннім з'єднанням, зазначеним в умові ON, і тільки рядки з іншої таблиці, де поля, що об'єднуються, рівні.

Синтаксис

Синтаксис Oracle RIGHT OUTER JOIN:

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

У деяких базах даних RIGHT OUTER JOIN замінюється на RIGHT JOIN.

На цьому малюнку Oracle RIGHT OUTER JOIN повертає затінену область:

Oracle RIGHT OUTER JOIN поверне всі записи з table2 і тільки записи з table1, які перетинаються з table2.

приклад

Нижче наведено приклад Oracle RIGHT OUTER JOIN:

Oracle PL/SQL

SELECT orders.order_id,

orders.order_date,

suppliers.supplier_name

FROM suppliers

RIGHT OUTER JOIN orders

ON suppliers.supplier_id=orders.supplier_id;

Цей приклад RIGHT OUTER JOIN повертає всі рядки з таблиці orders і лише ті рядки з таблиці suppliers , де поля, що об'єднуються, рівні.

Якщо значення supplier_id у таблиці orders не існує у таблиці suppliers , всі поля в таблиці suppliers будуть відображатися в результуючому наборі як NULL.

Розглянемо деякі дані, щоб зрозуміти, як працює RIGHT OUTER JOIN:

У нас є таблиця suppliers з двома полями (supplier_id та supplier_name ) яка містить такі дані:

Якщо виконаємо Oracle оператор SELECT (який містить RIGHT OUTER JOIN) нижче:

Oracle PL/SQL

SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers RIGHT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

Рядок для order_id 500127 буде включений, оскільки був використаний RIGHT OUTER JOINS. Тим не менш, ви помітите, що поле supplier_name для цього запису містить NULL.

Інший тип з'єднання називається Oracle FULL OUTER JOIN. Цей тип з'єднання повертає всі рядки з лівої та правої таблиці з NULL — значеннями в місці, де умова об'єднання не виконується.