Join завжди використовується з оператором on. Підтримувані Oracle типи з'єднань SQL: JOIN та інші. Синтаксис оператора JOIN
В 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 зазвичай опускається.
Раніше ми розглянули застосування інструкції 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.
У цьому прикладі відбувається вибірка відділів (з включенням повної інформації про них) для таких міст, де співробітники або тільки працюють, або проживають і працюють. Результат виконання цього запиту:
Крім лівого та правого зовнішнього з'єднання, також існує повне зовнішнє з'єднання, яке є об'єднанням лівого та правого зовнішніх з'єднань. Іншими словами, результуючий набір такого з'єднання складається зі всіх рядків обох таблиць. Якщо для рядка однієї з таблиць немає відповідного рядка в іншій таблиці, всім осередкам рядка другої таблиці надається значення 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 використовує операцію напівз'єднання для реалізації функціональності, що називається з'єднанням типу "зірка".
Оператор 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 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 tutorial explains how to use JOINS(inner and outer) в Oracle з syntax, visual illustrations, і examples.
Description
Oracle JOINS є використаним для редагування даних з декількох таблиць. У Oracle JOIN is performed whenever two or more tables are joined in SQL statement.
Є 4 різні типи Oracle joins:
- Oracle INNER JOIN (or sometimes called simple join)
- Oracle LEFT OUTER JOIN (або деякі називають LEFT JOIN)
- Oracle RIGHT OUTER JOIN (або деякі називається RIGHT JOIN)
- Oracle FULL OUTER JOIN (або деякі називають FULL JOIN)
З ним "розмовляє Oracle JOIN syntax", приводить до visual illustrations of Oracle JOINS, і explor Oracle JOIN examples.
INNER JOIN (simple join)
Зміни є, ви впевнені, що вказує на те, що використовує Oracle INNER JOIN. Це є найбільш загальний тип join.
Syntax
Syntax для INNER JOIN в Oracle/PLSQL is:
SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;
Visual Illustration
У цьому візуальному діаграмі, Oracle INNER JOIN повідомить пропорційну область:
Oracle INNER JOIN would return the records where table1 and table2 intersect.
Example
Here is an example of an Oracle INNER JOIN:
Цей Oracle INNER JOIN example може бути відновлений всі рядки з постачальниками і ордерами tables, які є здійснені значення supplier_id значення в межах постачальників і orders tables.
Let's look at some data to explain how the INNER JOINS work:
We have a table called suppliers
| supplier_id | supplier_name |
|---|---|
| 10000 | IBM |
| 10001 | Hewlett Packard |
| 10002 | Microsoft |
| 10003 | NVIDIA |
We have another table called orders
| order_id | supplier_id | order_date |
|---|---|---|
| 500125 | 10000 | 2003/05/12 |
| 500126 | 10001 | 2003/05/13 |
| 500127 | 10004 | 2003/05/14 |
Якщо ви робите Oracle SELECT statement (що містить INNER JOIN) нижче:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
| supplier_id | name | order_date |
|---|---|---|
| 10000 | IBM | 2003/05/12 |
| 10001 | Hewlett Packard | 2003/05/13 |
The rows for Microsoft and NVIDIAвід модуля таблиці було б заміщено, повідомлено про те, що supplier_id"s 10002 і 10003 до не існує в tables tables.
Old Syntax
Як результат, я маю на увазі, що у Oracle INNER JOIN example above could be rewritten using the oldder implicit syntax as follows (але we still recommend using the INNER JOIN keyword syntax):
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers, orders WHERE suppliers.supplier_id = orders.supplier_id;
LEFT OUTER JOIN
Інший тип join є названий Oracle LEFT OUTER JOIN. Цей тип join returns all rows from the LEFT-hand table specified in the ON condition and only
Syntax
The syntax for the Oracle LEFT OUTER JOIN is:
SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
У деяких databases, LEFT OUTER JOIN keywords є replaced with LEFT JOIN.
Visual Illustration
У цьому visual diagram, Oracle LEFT OUTER JOIN returns shaded area:
The Oracle LEFT OUTER JOIN буде відновити всі коментарі від table1 and only those records from table2 that intersect with table1.
Example
Тут є приклад з Oracle LEFT OUTER JOIN:
Цей LEFT OUTER JOIN приклад може бути відновлений всі рядки з таблиці модулів і тільки ті рядки з orders table, де спільні поля є еквівал.
Let's look at some data to explain how LEFT OUTER JOINS work:
We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:
| supplier_id | supplier_name |
|---|---|
| 10000 | IBM |
| 10001 | Hewlett Packard |
| 10002 | Microsoft |
| 10003 | NVIDIA |
orders with three fields (order_id, supplier_id, and order_date). It contains the following data:
| order_id | supplier_id | order_date |
|---|---|---|
| 500125 | 10000 | 2003/05/12 |
| 500126 | 10001 | 2003/05/13 |
Якщо ми робимо SELECT statement (що містить LEFT OUTER JOIN) нижче:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
Ваш результат має бути таким:
| supplier_id | supplier_name | order_date |
|---|---|---|
| 10000 | IBM | 2003/05/12 |
| 10001 | Hewlett Packard | 2003/05/13 |
| 10002 | Microsoft | |
| 10003 | NVIDIA |
The rows for Microsoft and NVIDIA would be included because a LEFT OUTER JOIN was used. However, ви будете повідомити, що order_date field for those records contains a
Old Syntax
Як для останнього зауважте, це є добре помітним, що LEFT OUTER JOIN example above could be rewritten using the old implicit syntax that used the outer join operator (+) as follows (but we still recomend using the LEFT OUT ER
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers, orders WHERE suppliers.supplier_id = orders.supplier_id(+);
RIGHT OUTER JOIN
Інший тип знімків називається Oracle RIGHT OUTER JOIN. Цей тип join returns all rows from the RIGHT-hand table specified in the ON condition and onlyці рядки з іншого table where the joined fields є equal (join condition is met).
Syntax
The syntax for the Oracle RIGHT OUTER JOIN is:
SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
У деяких 데이터베이스, RIGHT OUTER JOIN keywords є replaced with RIGHT JOIN.
Visual Illustration
У цьому візуальному діаграмі, Oracle RIGHT OUTER JOIN відновить shaded area:
Oracle RIGHT OUTER JOIN буде відновити всі коментарі від table2 and only those records from table1 that intersect with table2.
Example
Тут ви знайдете Oracle RIGHT OUTER JOIN:
Цей RIGHT OUTER JOIN example може бути відновлений всі рядки з таблиці орденів і тільки ті рядки з платників таблиці, де з'єднані поля є один.
Якщо supplier_id значення в orders table does no exist in the suppliers table, all fields in suppliers table will display as
Let's look at some data to explain how RIGHT OUTER JOINS work:
We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:
| supplier_id | supplier_name |
|---|---|
| 10000 | Apple |
| 10001 |
We have a second table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:
| order_id | supplier_id | order_date |
|---|---|---|
| 500125 | 10000 | 2013/08/12 |
| 500126 | 10001 | 2013/08/13 |
| 500127 | 10002 | 2013/08/14 |
Якщо ми робимо SELECT statement (що містить RIGHT OUTER JOIN) нижче:
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 | order_date | supplier_name |
|---|---|---|
| 500125 | 2013/08/12 | Apple |
| 500126 | 2013/08/13 | |
| 500127 | 2013/08/14 |
The row for 500127
(order_id) можна було б включати в RIGHT OUTER JOIN було використано. However, ви будете повідомити, що supplier_name field for that record contains a
Old Syntax
Як результат, я маю на увазі, що RIGHT OUTER JOIN example above could be rewritten using the old implicit syntax that used the outer join operator (+) as follows (but we still recomend using the RIGHT OUT
SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers, orders WHERE suppliers.supplier_id(+) = orders.supplier_id;
FULL OUTER JOIN
Інший тип знімків називається Oracle FULL OUTER JOIN. Цей тип join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.
Syntax
The syntax for the Oracle FULL OUTER JOIN is:
SELECT columns FROM table1 FULL JOIN table2 ON table1.column = table2.column;
У деяких 데이터베이스, FULL OUTER JOIN keywords є replaced with FULL JOIN.
Visual Illustration
У цьому візуальному діаграмі, Oracle FULL OUTER JOIN відновить shaded area:
The Oracle FULL OUTER JOIN буде відновити всі записи з both table1 and table2.
Example
Тут є прикладом Oracle FULL OUTER JOIN:
Цей FULL OUTER JOIN example would return all rows from suppliers table and all rows from the orders table and whenever the join condition is not met,
Якщо supplier_id значення в таблиці таблиці не існує в tables orders, всі поля в tables orders будуть відображатися як
Let's look at some data to explain how FULL OUTER JOINS work:
We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:
| supplier_id | supplier_name |
|---|---|
| 10000 | IBM |
| 10001 | Hewlett Packard |
| 10002 | Microsoft |
| 10003 | NVIDIA |
We have a second table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:
| order_id | supplier_id | order_date |
|---|---|---|
| 500125 | 10000 | 2013/08/12 |
| 500126 | 10001 | 2013/08/13 |
| 500127 | 10004 | 2013/08/14 |
Якщо ми робимо SELECT statement (що містить FULL OUTER JOIN) нижче:
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers FULL OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
Ваш результат має бути таким:
| supplier_id | supplier_name | order_date |
|---|---|---|
| 10000 | IBM | 2013/08/12 |
| 10001 | Hewlett Packard | 2013/08/13 |
| 10002 | Microsoft | |
| 10003 | NVIDIA | |
| 2013/08/14 |
The rows for Microsoft and NVIDIA would be included because a FULL OUTER JOIN was used. However, ви будете повідомити, що order_date field for those records contains a
row for supplier_id 10004 може бути також включений тому, що FULL OUTER JOIN був використаний. However, ви будете повідомити, що supplier_id і supplier_name field for those records contain a
Old Syntax
Як для останнього зауваження, це є приємним ментіонітом, що FULL OUTER JOIN example above could not been written in the old syntax without using a
Цей навчальний посібник 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 — значеннями в місці, де умова об'єднання не виконується.
Синтаксис
Синтаксис для Oracle FULL OUTER JOIN:
SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;
У деяких базах даних FULL OUTER JOIN замінюються FULL JOIN.
На цьому малюнку, FULL OUTER JOIN повертає затінену область:
Oracle FULL OUTER JOIN повертатиме всі записи з обох таблиць table1 і table2.
