Oracle типы соединений таблиц
Обновлено: 03.07.2024
При запросах к базе данных часто необходимо делать объединение, пересечение и вычитание запросов.
Следующий этап научиться объединять запросы, выполнять над ними операции пересечения и вычитания.
Оператор JOIN
Для выбора данных из нескольких таблиц необходимо перечислить имена требуемых таблиц в предложении FROM через запятую. В этом случае получим декартово произведение таблиц.
База данных, используемая в примерах, находится в этом посте.
Существует два способа соединения таблиц:
1) Условие соединения указывается в предложении WHERE
Пример 1.
Пусть требуется вывести информацию о поставках в виде отношения R(pnum, pname, dnum, volume).
2) Условие соединения указывается в предложении FROM
Синтаксис такого соединения следующий:
В соответствии с приведенным выше синтаксисом возможны следующие варианты соединения таблиц:
- Внутреннее соединение
- Внешнее соединение(левое, правое и полное)
Внутреннее соединение
С помощью внутреннего соединения выше рассмотренный пример можно записать следующим образом:
Соединять можно и более двух таблиц.
Пример 2.
Пусть требуется вывести информацию о поставках в виде отношения R(pname, dname, volume) .
Пример 3.
Пусть требуется вывести информацию о поставках в виде отношения R(pname, dname, dprice, volume, cost) , где значения поля стоимость поставки cost определяются как dprice*volume .
| pname | dname | dprice | volume | cost |
|---|---|---|---|---|
| Иванов | Болт | 10 руб. | 100 | 1000 руб. |
При построении вычисляемых полей для строковых значений может быть использована операция конкатенации (соединения) строк, которая записывается с помощью символа & или + . Эта операция была использована для получения значений вычисляемых полей dprice и cost . Предварительно числовые значения были приведены к строке с помощью функции STR() .
Внешние соединение
Различают 3 вида внешних соединений:
- Левое внешнее соединение
- Правое внешнее соединение
- Полное внешнее соединение
Левое внешнее соединение
Левое внешнее соединение отношений А и В отличается от внутреннего тем, что в результирующее отношение добавляются все кортежи из отношения А (левой таблицы), при этом отсутствующие значения полей из отношения В (правой таблицы) будут заполняться NULL-значениями.
Полное внешние соединение
При выполнении полного внешнего соединения в результирующее отношение помещаются все кортежи из обоих отношений, неизвестные значения заполняются NULL-значениями.
Правое внешнее соединение
Правое соединение выполняется аналогично левому, но в результирующее отношение добавляются все кортежи из отношения B (правой таблицы).
Оператор UNION – Объединение
Для объединения запросов используется следующий синтаксис:
По умолчанию оператор UNION удаляет повторяющиеся строки из результирующего набора. Если указан параметр ALL, то результат будет содержать все строки, в том числе повторяющиеся.
Объединяемые запросы должны быть совместимы между собой. В терминологии SQL это обозначает, что:
- Таблицы должны иметь одинаковое количество столбцов.
- Типы данных в соответствующих столбцах должны быть совместимыми.
Объединяемые запросы должны быть совместимы между собой. В терминологии SQL это обозначает, что:
- Таблицы должны иметь одинаковое количество столбцов.
- Типы данных в соответствующих столбцах должны быть совместимыми.
При объединении данных из столбцов с разными именами результирующему столбцу присваивается имя столбца из первого запроса.
К результату объединения рекомендуется применять предложение ORDER BY , где можно ссылаться только на имена столбцов левого запроса в операторе UNION .
Пример 4.
Пусть задана таблица P1.
| Номер | Наименование |
|---|---|
| 5 | Орлов |
Определим результат следующего объединения:
| Номер | Наименование |
|---|---|
| 1 | Иванов |
| 5 | Орлов |
Пример 5.
Получить номера деталей, цена которых более 20 рублей или суммарное поставляемое количество более 500 штук.
Запрос разбивается на две части:
- Вывод номеров деталей, цена которых более 200 рублей.
- Вывод номеров деталей, которые поставляются в количестве более 500 штук.
Результирующая таблица получается при объединении двух частей запроса.
Пример 6.
Вывести информацию о деталях. В том случае если цена детали не указана вывести ‘цены нет’.
Запрос разбивается на две части:
- Вывод информации о деталях, для которых указана цена.
- Вывод информации о деталях, для которых не указана цена. В этом случае в предложении SELECT вместо атрибута dprice нужно указывать строковую константу ‘цены нет’.
Оператор INTERSECT – Пересечение
Пересечение запросов выполняется с помощью оператора INTERSECT , который выполняется аналогично оператору UNION .
Пример 7.
Вывести номера деталей, которые поставляет и поставщик с номером 1, и поставщик с номером 2.
Оператор EXCEPT – Вычитание
Вычитание выполняется с помощью оператора EXCEPT , который выполняется аналогично оператору UNION .
Пример 8.
Вывести номера поставщиков, которые не поставляют детали в настоящее время.
Пример 9.
Вывести номера поставщиков, которые не поставляют деталь № 2.
Пример 10.
Вывести номера поставщиков, которые поставляют только деталь № 1.
Oracle JOINS используются для извлечения данных из нескольких таблиц. JOIN выполняется всякий раз, когда две или более таблиц объединяются в SQL предложении.
Есть 4 различных типа присоединения Oracle:
Рассмотрим синтаксис Oracle JOIN, а также изучим примеры Oracle JOIN.
INNER JOIN (простое соединение)
Скорее всего, вы уже писали запросы в которых используются Oracle INNER JOIN. Это наиболее распространенный тип соединения. Oracle INNER JOINS возвращает все строки из нескольких таблиц, где выполняется условия соединения.
Синтаксис
Синтаксис INNER JOIN в Oracle/PLSQL:
SELECT columnsFROM table1
INNER JOIN table2
ON table1.column = table2.column;
В этом рисунке, Oracle INNER JOIN возвращает затененную область:
Oracle INNER JOIN будет возвращать записи, где table1 и table2 будут пересекаться.
Пример
Ниже приведен пример Oracle INNER JOIN:
Этот пример Oracle INNER JOIN возвращает все строки из таблиц suppliers и orders , где имеются соответствующие значение поля supplier_id в обоих таблицах.
Рассмотрим некоторые данные, чтобы понять, как работает INNER JOIN:
У нас есть таблица suppliers с двумя полями ( supplier_id и supplier_name ) которая содержит следующие данные:
| supplier_id | supplier_name |
|---|---|
| 10000 | IBM |
| 10001 | Hewlett Packard |
| 10002 | Microsoft |
| 10003 | NVIDIA |
У нас есть еще одна таблица orders с тремя полями ( order_id , supplier_id и order_date ). Она содержит следующие данные:
| order_id | supplier_id | order_date |
|---|---|---|
| 500125 | 10000 | 05.05.2015 |
| 500126 | 10001 | 08.02.2016 |
| 500127 | 10004 | 06.01.2017 |
Если мы выполним Oracle оператор SELECT (который содержит INNER JOIN) ниже:
Наш результирующий набор будет выглядеть следующим образом:
| supplier_id | name | order_date |
|---|---|---|
| 10000 | IBM | 05.05.2015 |
| 10001 | Hewlett Packard | 08.02.2016 |
Строки для Microsoft и NVIDIA из таблицы suppliers будут опущены, так как значения supplier_id 10002 и 10003 не существует в обеих таблицах. Строка order_id 500127 из таблицы orders будет опущена, так как supplier_id 10004 не существует в таблице suppliers .
Старый Синтаксис
В качестве последнего примечания, стоит отметить, что приведенный выше пример Oracle INNER JOIN можно переписать, используя старый неявный синтаксис следующим образом (но рекомендуется использовать синтаксис INNER JOIN):
SELECT suppliers.supplier_id,suppliers.supplier_name,
orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;
LEFT OUTER JOIN
Другой тип соединения называется Oracle LEFT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с левосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны.
Синтаксис
Синтаксис для Oracle LEFT OUTER JOIN:
SELECT columnsFROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
В некоторых базах данных LEFT OUTER JOIN заменяется на LEFT JOIN.
На этом рисунке, Oracle LEFT OUTER JOIN возвращает затененную область:
Oracle LEFT OUTER JOIN возвратит все записи из table1 и только те записи из table2 , которые пересекаются с table1 .
Пример
Этот пример LEFT OUTER JOIN возвратит все строки из таблицы suppliers , и только те строки из таблицы orders , где объединяемые поля равны.
Если значение supplier_id в таблице suppliers не существует в таблице orders , все поля таблицы orders будут отображаться в результирующем наборе как NULL.
Рассмотрим некоторые данные, чтобы понять, как работает LEFT OUTER JOIN:
У нас есть таблица suppliers с двумя полями ( supplier_id и supplier_name ) которая содержит следующие данные:
| supplier_id | supplier_name |
|---|---|
| 10000 | IBM |
| 10001 | Hewlett Packard |
| 10002 | Microsoft |
| 10003 | NVIDIA |
У нас есть еще одна таблица orders с тремя полями ( order_id , supplier_id и order_date ). Она содержит следующие данные:
| order_id | supplier_id | order_date |
|---|---|---|
| 500125 | 10000 | 05.05.2015 |
| 500126 | 10001 | 08.02.2016 |
Если мы выполним Oracle оператор SELECT (который содержит LEFT OUTER JOIN) ниже:
Результирующий набор будет выглядеть следующим образом:
| supplier_id | name | order_date |
|---|---|---|
| 10000 | IBM | 05.05.2015 |
| 10001 | Hewlett Packard | 08.02.2016 |
| 10002 | Microsoft | null |
| 10003 | NVIDIA | null |
Строки для Microsoft и NVIDIA будут включены, так как был использован LEFT OUTER JOIN. Тем не менее, вы заметите, что поле order_date для этих записей содержит значение NULL.
RIGHT OUTER JOIN
Другой тип соединения называется Oracle RIGHT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с правосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны.
Синтаксис
Синтаксис Oracle RIGHT OUTER JOIN:
SELECT columnsFROM table1
RIGHT [OUTER] 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:
Этот пример RIGHT OUTER JOIN возвращает все строки из таблицы orders и только те строки из таблицы suppliers , где объединяемые поля равны.
Если значение supplier_id в таблице orders не существует в таблице suppliers , все поля в таблице suppliers будут отображаться в результирующем наборе как NULL.
Рассмотрим некоторые данные, чтобы понять, как работает RIGHT OUTER JOIN:
У нас есть таблица suppliers с двумя полями ( supplier_id и supplier_name ) которая содержит следующие данные:
| supplier_id | supplier_name |
|---|---|
| 10000 | Apple |
| 10001 |
У нас есть вторая таблица orders с тремя полями ( order_id , supplier_id и order_date ). Она содержит следующие данные:
| order_id | supplier_id | order_date |
|---|---|---|
| 500125 | 10000 | 12.05.2016 |
| 500126 | 10001 | 14.05.2016 |
| 500127 | 10002 | 18.05.2016 |
Если мы выполним Oracle оператор SELECT (который содержит RIGHT OUTER JOIN) ниже:
Результирующий набор будет выглядеть следующим образом:
| order_id | order_date | supplier_name |
|---|---|---|
| 500125 | 12.05.2016 | Apple |
| 500126 | 14.05.2016 | |
| 500127 | 18.05.2016 | null |
Строка для order_id 500127 будет включена, так как был использован RIGHT OUTER JOINS. Тем не менее, вы заметите, что поле supplier_name для этой записи содержит значение NULL.
FULL OUTER JOIN
Другой тип соединения называется Oracle FULL OUTER JOIN. Этот тип соединения возвращает все строки из левой таблицы и правой таблицы с NULL - значениями в месте, где условие объединения не выполняется.
Синтаксис
Синтаксис для Oracle FULL OUTER JOIN:
SELECT columnsFROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;
В некоторых базах данных, FULL OUTER JOIN заменяются FULL JOIN.
На этом рисунке, FULL OUTER JOIN возвращает затененную область:
Oracle FULL OUTER JOIN будет возвращать все записи из обеих таблиц table1 и table2 .
Пример
Ниже приведен пример Oracle FULL OUTER JOIN:
Этот пример FULL OUTER JOIN возвратит все строки из таблицы suppliers и все строки из таблицы orders и всякий раз, когда условие соединения не выполняется, то поля в результирующем наборе будут принимать значения NULL.
Если значение поля supplier_id в таблице suppliers не существует в таблице orders , то все поля в таблице orders будут отображаться в результирующем наборе как NULL. Если значение supplier_id в таблице orders не существует в таблице suppliers , то все поля в таблице suppliers будут отображаться результирующем наборе как NULL .
Рассмотрим некоторые данные, чтобы понять, как работает FULL OUTER JOIN:
У нас есть таблица suppliers с двумя полями ( supplier_id и supplier_name ). Она содержит следующие данные:
| supplier_id | supplier_name |
|---|---|
| 10000 | IBM |
| 10001 | Hewlett Packard |
| 10002 | Microsoft |
| 10003 | NVIDIA |
У нас есть вторая таблица orders с тремя полями ( order_id , supplier_id и order_date ), которая содержит следующие данные:
| order_id | supplier_id | order_date |
|---|---|---|
| 500125 | 10000 | 12.05.2016 |
| 500126 | 10001 | 14.05.2016 |
| 500127 | 10004 | 18.05.2016 |
Если мы выполним Oracle оператор SELECT (который содержит FULL OUTER JOIN) ниже:
Этот тип соединения отличается тем, что выборка выполняется из одной и той же таблице для нескольких условий.
SELECTE1.last_name, E1.title, E2.last_name, E2.title FROM
emp E1, emp E2 WHERE
E1.manager_id = E2.id; LAST_NAME TITLE LAST_NAME TITLE
-------------- ------------------------ --------------------- ------------------
Ngao VP, Operations Velasquez President
Nagayama VP, Sales Velasquez President
Quick-To-See VP, Finance Velasquez President
Ropeburn VP, Administration Velasquez President
Urguhart Warehouse Manager Ngao VP, Operations
Menchu Warehouse Manager Ngao VP, Operations
Biri Warehouse Manager Ngao VP, Operations
Catchpole Warehouse Manager Ngao VP, Operations
Havel Warehouse Manager Ngao VP, Operations
Magee Sales Representative Nagayama VP, Sales
Giljum Sales Representative Nagayama VP, Sales
Sedeghi Sales Representative Nagayama VP, Sales
Nguyen Sales Representative Nagayama VP, Sales
Dumas Sales Representative Nagayama VP, Sales
Maduro Stock Clerk Urguhart Warehouse Manager
Smith Stock Clerk Urguhart Warehouse Manager
Nozaki Stock Clerk Menchu Warehouse Manager
Palet Stock Clerk Menchu Warehouse Manager
Newman Stock Clerk Biri Warehouse Manager
Markarian Stock Clerk Biri Warehouse Manager
Chang Stock Clerk Catchpole Warehouse Manager
Palet Stock Clerk Catchpole Warehouse Manager
Dancs Stock Clerk Havel Warehouse Manager
Schwartz Stock Clerk Havel Warehouse Manager
Комментарий:
В первом столбце показываем фамилию сотрудников, во втором должность на которой работает, в третем столбце фамилия начальника и в четвертому должность на которой работает. Обратите внимание, что, когда в предложении WHERE изменить условие E2.manager_id = E1.id, получим в результате другую сортировку данных. В первых двух столбцах будут данные начальства а в остальных двох данные работников.
В Oracle поддерживается несколько типов соединений, отличающихся способом, которым производится объединение строк из двух или более таблиц или представлений. В этой заметке моего блога будут описаны типы соединений, применяемые в Oracle наиболее часто.
Эквисоединение
При эквисоединении (equi-join) две или более таблиц соединяются на основании условия равенства между столбцами. Другими словами, один и тот же столбец имеет одинаковое значение во всех соединяемых таблицах. Ниже приведен пример применения эквисоединения:
Для показанного выше оператора соединения также можно использовать и следующий новый синтаксис:
При желании соединить несколько столбцов, можно перечислить их имена в виде разделенного запятыми списка, например: USING ( dept_id , emp_name ).
Естественное соединение
Естественным соединением (natural join) называется эквисоединение, при котором столбцы, которые должны сопоставляться для выполнения соединения, специально не указываются. Oracle автоматически определяет подлежащие соединению столбцы на основании совпадающих столбцов в двух таблицах. Ниже приведен пример применения естественного соединения:
В этом примере условием для выполнения соединения служит наличие идентичных значений в столбце last_name в таблицах emp и dept .
Рефлексивное соединение
Под рефлексивным соединением (self join) подразумевается соединение таблицы с самой собой за счет использования псевдонимов. В следующем примере осуществляется соединение таблицы employees с самой собой при помощи псевдонима с удалением всех дублированных строк.
Внутреннее соединение
Внутреннее соединение (inner join), также называемое простым соединением (simple join), предусматривает возврат всех строк, которые удовлетворяют указанному условию соединения. Раньше в синтаксисе внутреннего соединения для указания того, каким образом должны соединяться таблицы, нужно было использовать конструкцию WHERE , например, так:
Теперь Oracle позволяет задавать критерии соединения в синтаксисе внутреннего (или простого) соединения за счет применения новой конструкции ON или USING , например:
Внешнее соединение
Внешнее соединение (outer join) применяется для возврата всех строк, которые удовлетворяют указанному условию соединения, плюс некоторых или всех строк из таблицы, в которой нет подходящих строк, удовлетворяющих указанному условию соединения. Существуют три вида внешнего соединения: левое внешнее соединение (left outer join), правое внешнее соединение (right outer join) и полное внешнее соединение (full outer join). В операторе полного внешнего соединения слово OUTER обычно опускается.
Oracle позволяет использовать операцию внешнего соединения, подразумевающую применение знака плюс (+) для обозначения недостающих значений в одной таблице, но рекомендует лучше использовать вместо нее более новый синтаксис соединения ISO/ANSI. Ниже приведен пример типичного запроса с оператором полного внешнего соединения:
Читайте также: