Раніше ми розглянули застосування інструкції 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 використовує операцію напівз'єднання для реалізації функціональності, що називається з'єднання типу "зірка".
Продовжуємо вивчати основи 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 as t1_number,t1.text as t1_text, t2.number as t2_number, t2.text as 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 as t1_number,t1.text as t1_text, t2.number as t2_number, t2.text as 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.num
А тепер давайте спробуємо об'єднати наші таблиці за двома ключами, для цього трохи згадаємо, як додавати колонку в таблицю і як оновити дані через 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 as t1_number,t1.text as t1_text, t2.number as t2_number, t2.text as 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 as t1_number,t1.text as t1_text, t2.number as t2_number, t2.text as 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 a t4. 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 не закінчено в наступних статтях ми продовжимо, а поки що тренуйтеся і пишіть свої запити. Успіхів!
Оператор JOINвикористовується для виконання операції з'єднання даних з двох наборів один результуючий набір. Може бути використано кілька різних типівоперацій з'єднання під час виконання SELECT . Які рядки увійдуть в результуючий набір залежить від типу операції з'єднання та від певної умови з'єднання. Умова сполуки, тобто. умова зіставлення рядків таблиць один з одним, є логічний вираз.
За необхідності об'єднання кількох таблиць операція з'єднання має застосовуватися послідовно кілька разів.
Синтаксис оператора JOIN
Синтаксис підключення до запиту додаткової таблиці за допомогою оператора JOINможна уявити у такому вигляді:
SELECT field1, field1, [,... n] FROM Table1 t1 (INNER | (LEFT | RIGHT | FULL) OUTER | CROSS ) JOIN Table2 (ON
У більшості СУБД під час використання оператора 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 into users (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 in autos (id, oid, name) values (5, 4, "suzuki swift"); insert into 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 renault x-trail olga suzuki swift olga suzuki vitara -- Запит без вибору колонок результуючого набору select * from users u inner join autos on . 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 a ON a.oid = u.id -- Результат запиту owner auto alex toyota camry alex toyota prado piter renault olga suzuki vitara ostap
Графічно результат роботи можна так:
Оператор LEFT OUTER JOIN із фільтрацією
Додавши до коду попереднього прикладу умову "where a.name is null". У вибірці залишиться лише один запис "ostap", тому що тільки у нього не визначено автомобіль.
Графічно результат роботи можна так:
Оператор перехресного з'єднання, CROSS JOIN
CROSS JOIN - це оператор перехресного з'єднання (декартове твір). Оператор є симетричним та порядок таблиць для оператора неважливий.
Результатуючий набір формується наступним чином: кожен рядок однієї таблиці з'єднується з кожним рядком другої таблиці, даючи цим в результаті всі можливі поєднання рядків двох таблиць. Для нашого прикладу це виходить 30 рядків результуючого набору.
Select * від 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 cam 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 рядків.
Операція з'єднання, як і інші бінарні операції, призначена для забезпечення вибірки даних із двох таблиць та включення цих даних в один результуючий набір. Відмінною особливістюоперації з'єднання є таке:
- до схеми таблиці-результату входять стовпці обох вихідних таблиць (таблиць-операндів), тобто схема результату є «зчепленням» схем операндів;
- кожен рядок таблиці-результату є "зчепленням" рядка з однієї таблиці-операнда з рядком другої таблиці-операнда.
Визначення того, які саме вихідні рядки увійдуть у результат та в яких поєднаннях, залежить від типу операції з'єднання та від явно заданого умови з'єднання. Умова з'єднання, тобто умова зіставлення рядків вихідних таблиць один з одним, є логічним виразом (предикат).
При необхідності з'єднання не двох, а кількох таблиць операція з'єднання застосовується кілька разів (послідовно).
Опис оператора
SELECT FIELD [ ,... n] FROM Table1 ( INNER | ( LEFT | RIGHT | FULL ) OUTER | CROSS ) JOIN Table2 ON< condition>
У більшості СУБД за вказівкою слів LEFT, RIGHT, FULL слово OUTER можна опустити. Слово INNER також у більшості СУБД можна опустити.
У випадку СУБД під час виконання з'єднання перевіряє умова (предикат) condition. CROSS JOIN умова не вказується.
Для перехресного з'єднання (декартового твору) CROSS JOIN у деяких реалізаціях SQL використовується оператор «кома» ( , ):
SELECT FIELD [ ,... n] FROM Table1, Table2
Види оператора JOIN
Для подальших пояснень будуть використовуватись такі таблиці:
Люди, які мешкають у містах (таблиця Person)
INNER JOIN
Оператор внутрішнього з'єднання INNER JOIN поєднує дві таблиці. Порядок таблиць для оператора є неважливим, оскільки оператор є симетричним .
Заголовокконкатенацією
Тілорезультату логічно формується в такий спосіб. Кожен рядок однієї таблиці зіставляється з кожним рядком другої таблиці, після чого для отриманого "з'єднаного" рядка перевіряється умова з'єднання (обчислюється предикат з'єднання). Якщо умова істинна, до таблиці-результату додається відповідний «з'єднаний» рядок.
Описаний алгоритм дій є суворо логічним, тобто він лише пояснює результат, який повинен вийти при виконанні операції, але не наказує, щоб конкретна СУБД виконувала з'єднання саме вказаним чином. Існує безліч способів реалізації операції з'єднання, наприклад, з'єднання вкладеними циклами (англ. inner loops join), з'єднання хешуванням (англ. hash join), з'єднання злиттям (англ. merge join). Єдина вимога полягає в тому, щоб будь-яка реалізація логічно давала такий самий результат, як при застосуванні описаного алгоритму.
SELECT * FROM Person INNER JOIN City ON Person. CityId = City. Id
Результат:
| Person.Name | Person.CityId | City.Id | City.Name |
|---|---|---|---|
| Андрій | 1 | 1 | Москва |
| Леонід | 2 | 2 | Санкт-Петербург |
| Сергій | 1 | 1 | Москва |
OUTER JOIN
З'єднання двох таблиць, в результаті якого обов'язково входять рядки або однієї, або обох таблиць.
LEFT OUTER JOIN
Оператор лівого зовнішнього з'єднання LEFT OUTER JOIN поєднує дві таблиці. Порядок таблиць для оператора є важливим, оскільки оператор не є симетричним .
Заголовоктаблиці-результату є об'єднанням (конкатенацією) заголовків таблиць, що з'єднуються.
Тіло p.
- p.
- Потім результат додаються ті записи лівої таблиці, які увійшли у внутрішнє з'єднання на кроці 1. Для таких записів поля, відповідні правої таблиці, заповнюються значеннями NULL.
SELECT * FROM Person LEFT OUTER JOIN City ON Person. CityId = City. Id
Результат:
| Person.Name | Person.CityId | City.Id | City.Name |
|---|---|---|---|
| Андрій | 1 | 1 | Москва |
| Леонід | 2 | 2 | Санкт-Петербург |
| Сергій | 1 | 1 | Москва |
| Григорій | 4 | NULL | NULL |
RIGHT OUTER JOIN
Оператор правого зовнішнього з'єднання RIGHT OUTER JOIN поєднує дві таблиці. Порядок таблиць для оператора є важливим, оскільки оператор не є симетричним .
Заголовоктаблиці-результату є об'єднанням (конкатенацією) заголовків таблиць, що з'єднуються.
Тілорезультату логічно формується в такий спосіб. Нехай виконується з'єднання лівої та правої таблиць за предикатом (умовою) p.
- В результат включається внутрішнє з'єднання (INNER JOIN) лівої та правої таблиць за предикатом p.
- Потім до результату додаються ті записи правої таблиці, які не увійшли у внутрішнє з'єднання на кроці 1. Для таких записів поля, відповідні лівій таблиці, заповнюються значеннями NULL.
SELECT * FROM Person RIGHT OUTER JOIN City ON Person. CityId = City. Id
Результат:
| Person.Name | Person.CityId | City.Id | City.Name |
|---|---|---|---|
| Андрій | 1 | 1 | Москва |
| Сергій | 1 | 1 | Москва |
| Леонід | 2 | 2 | Санкт-Петербург |
| NULL | NULL | 3 | Казань |
FULL OUTER JOIN
Оператор повного зовнішнього з'єднання FULL OUTER JOIN поєднує дві таблиці. Порядок таблиць для оператора є неважливим, оскільки оператор є симетричним .
Заголовоктаблиці-результату є об'єднанням (конкатенацією) заголовків таблиць, що з'єднуються.
Тілорезультату логічно формується в такий спосіб. Нехай виконується з'єднання першої та другої таблиць за предикатом (умовою) p. Слова «перший» і «другий» тут не позначають порядок у запису (який неважливий), а застосовуються лише для розрізнення таблиць.
- В результат включається внутрішнє з'єднання (INNER JOIN) першої та другої таблиць за предикатом p.
- В результат додаються ті записи першої таблиці, які не увійшли у внутрішнє з'єднання на кроці 1. Для таких записів поля, що відповідають другій таблиці, заповнюються значеннями NULL.
- У результат додаються ті записи другої таблиці, які не увійшли у внутрішнє з'єднання на кроці 1. Для таких записів поля, що відповідають першій таблиці, заповнюються значеннями NULL.
SELECT * FROM Person FULL OUTER JOIN City ON Person. CityId = City. Id
Результат:
| Person.Name | Person.CityId | City.Id | City.Name |
|---|---|---|---|
| Андрій | 1 | 1 | Москва |
| Сергій | 1 | 1 | Москва |
| Леонід | 2 | 2 | Санкт-Петербург |
| NULL | NULL | 3 | Казань |
| Григорій | 4 | NULL | NULL |
CROSS JOIN
Оператор перехресного з'єднання, або декартові твори CROSS JOIN поєднує дві таблиці. Порядок таблиць для оператора є неважливим, оскільки оператор є симетричним .
Заголовоктаблиці-результату є об'єднанням (конкатенацією) заголовків таблиць, що з'єднуються.
Тілорезультату логічно формується в такий спосіб. Кожен рядок однієї таблиці з'єднується з кожним рядком другої таблиці, даючи цим у результаті всі можливі поєднання рядків двох таблиць.
SELECT * FROM Person CROSS JOIN City
SELECT * FROM Person, City
Результат:
| Person.Name | Person.CityId | City.Id | City.Name |
|---|---|---|---|
| Андрій | 1 | 1 | Москва |
| Андрій | 1 | 2 | Санкт-Петербург |
| Андрій | 1 | 3 | Казань |
| Леонід | 2 | 1 | Москва |
| Леонід | 2 | 2 | Санкт-Петербург |
| Леонід | 2 | 3 | Казань |
| Сергій | 1 | 1 | Москва |
| Сергій | 1 | 2 | Санкт-Петербург |
| Сергій | 1 | 3 | Казань |
| Григорій | 4 | 1 | Москва |
| Григорій | 4 | 2 | Санкт-Петербург |
| Григорій | 4 | 3 | Казань |
Якщо в пропозиції WHERE додати умову з'єднання, тобто обмеження на поєднання кортежів, то результат еквівалентний операції INNER JOIN з такою самою умовою:
Таким чином, CROSS JOIN + WHERE ( предикат) та INNER JOIN( предикат) синтаксично є альтернативними формами запису однієї і тієї ж логічної операціївнутрішньої сполуки. Синтаксис CROSS JOIN + WHERE для операції з'єднання називають застарілим, оскільки його не рекомендує стандарт SQL ANSI.
Примітки
Посилання
- Опис видів Join в Oracle (рус.)
- Jeff Atwood, A Visual Explanation of SQL Joins (англ.)
| SQL | |
|---|---|
| Версії | SQL-86 SQL-89 SQL-92 SQL:1999 SQL:2003 SQL:2008 |
| Ключові слова | Create Delete From Having Insert Join Merge Null Order by Prepare Select Top Truncate Union Update |
| Related | Едгар Кодд Реляційна база даних |
В Oracle підтримується кілька типів з'єднань, що відрізняються способом, яким здійснюється об'єднання рядків з двох або більше таблиць або уявлень. У цій нотатці мого блогу будуть описані типи з'єднань, які найчастіше використовують Oracle.
Еквісполучення
При еквісполуканні (equi-join) дві або більше таблиць з'єднуються на підставі умови рівності між стовпцями. Іншими словами, один і той же стовпець має однакове значення у всіх таблицях, що з'єднуються. Нижче наведено приклад застосування еквісполуки:
SQL> SELECT e.last_name, d.dept FROM emp e, dept d WHERE e.emp_id = d.emp_id;
Для показаного вище оператора з'єднання також можна використовувати наступний новий синтаксис:
SQL> SELECT e.last_name, d.dept FROM emp e JOIN dept d USING (emp_id);
За бажання з'єднати кілька стовпців, можна перерахувати їх імена як розділеного комами списку, наприклад: USING (dept_id , emp_name).
Природне поєднання
Природним з'єднанням (natural join) називається еквісполука, у якому стовпці, які мають зіставлятися виконання з'єднання, спеціально не вказуються. Oracle автоматично визначає стовпці, що підлягають з'єднанню на підставі збігаються стовпців у двох таблицях. Нижче наведено приклад застосування природної сполуки:
SQL> SELECT e.last_name, d.dept FROM emp e NATURAL JOIN dept d;
У цьому прикладі умовою для виконання з'єднання є наявність ідентичних значеньу стовпці last_name у таблицях emp та dept .
Рефлексивне з'єднання
Під рефлексивним з'єднанням (self join) мається на увазі з'єднання таблиці із собою за рахунок використання псевдонімів. У наступному прикладі здійснюється з'єднання таблиці employees з самою собою за допомогою псевдоніма з видаленням всіх дубльованих рядків.
SQL> DELETE FROM employees X WHERE ROWID > 2 (select MIN(rowid) FROM employees Y 3 where X.key_values = Y.key_values);
Внутрішнє з'єднання
Внутрішнє з'єднання (inner join), також зване простим з'єднанням (simple join), передбачає повернення всіх рядків, які відповідають умові з'єднання. Раніше в синтаксисі внутрішнього з'єднання для вказівки того, як повинні з'єднуватися таблиці, потрібно було використовувати конструкцію WHERE , наприклад, так:
SQL> SELECT e.flast_name, d.dept FROM emp e, dept d WHERE e.emp_id = d.emp_id;
Тепер Oracle дозволяє задавати критерії з'єднання в синтаксисі внутрішнього (або простого) з'єднання за рахунок застосування нової конструкції ON або USING, наприклад:
SQL> SELECT DISTINCT NVL(dname, "No Dept"), COUNT(empno) nbr_emps FROM emp JOIN DEPT ON emp.deptno = dept.deptno WHERE emp.job IN ("MANAGER", "SALESMAN", "ANALYST") GROUP BY dname;
Зовнішнє з'єднання
Зовнішнє з'єднання (outer join) застосовується для повернення всіх рядків, які задовольняють зазначеній умові з'єднання, плюс деяких або всіх рядків з таблиці, в якій немає відповідних рядків, що задовольняють зазначеній умові з'єднання. Існують три види зовнішнього з'єднання: ліве зовнішнє з'єднання (left outer join), праве зовнішнє з'єднання (right outer join) та повне зовнішнє з'єднання (full outer join). В операторі повного зовнішнього з'єднання слово OUTER зазвичай опускається.
