Упрощенная запись left outer join. Поддерживаемые Oracle типы соединений в SQL: JOIN и другие
Это учебное пособие 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 .
Оператор языка SQL JOIN предназначен для соединения двух или более таблиц базы данных по совпадающему условию. Этот оператор существует только в реляционных базах данных. Именно благодаря JOIN реляционные базы данных обладают такой мощной функциональностью, которая позволяет вести не только хранение данных, но и их, хотя бы простейший, анализ с помощью запросов. Разберём основные нюансы написания SQL-запросов с оператором JOIN, которые являются общими для всех СУБД (систем управления базами данных). Для соединения двух таблиц оператор SQL JOIN имеет следующий синтаксис:
SELECT ИМЕНА_СТОЛБЦОВ (1..N) FROM ИМЯ_ТАБЛИЦЫ_1 JOIN ИМЯ_ТАБЛИЦЫ_2 ON УСЛОВИЕ
После одного или нескольких звеньев с оператором JOIN может следовать необязательная секция WHERE или HAVING, в которой, также, как в простом SELECT-запросе, задаётся условие выборки. Общим для всех СУБД является то, что в этой конструкции вместо JOIN может быть указано INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN (или, как вариант, запятая).
INNER JOIN (внутреннее соединение)
Запрос с оператором INNER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON.
То же самое делает и просто JOIN. Таким образом, слово INNER - не обязательное.
Пример 1. Есть база данных портала объявлений. В ней есть таблица Categories (категории объявлений) и Parts (части, или иначе - рубрики, которые и относятся к категориям). Например, части Квартиры, Дачи относятся к категории Недвижимость, а части Автомобили, Мотоциклы - к категории Транспорт. Эти таблицы с заполненными данными имеют следующий вид.
Таблица Parts:
Заметим, что в таблице Parts Книги имеют Cat - ссылку на категорию, которой нет в таблице Categories, а в таблице Categories Техника имеет Cat_ID - первичный ключ, ссылки на который нет в таблице Parts. Требуется соединить данные этих двух таблиц так, чтобы в результирующей таблице были поля Part (Часть), Cat (Категория) и Price (Цена подачи объявления) и чтобы данные полностью пересекались по условию. Условие - совпадение идентификатора категории в таблице Categories и ссылки на категорию в таблице Parts. Для этого пишем следующий запрос:
SELECT PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Price FROM PARTS INNER JOIN CATEGORIES ON PARTS.Cat = CATEGORIES.Cat_ID
| Part | Cat | Price |
| Квартиры | 505 | 210,00 |
| Автомашины | 205 | 160,00 |
| Доски | 10 | 105,00 |
| Шкафы | 30 | 77,00 |
В результирующей таблице нет Книг, так как эта запись ссылается на категорию, которой нет в таблице Categories, и Техники, так как эта запись имеет внешний ключ в таблице Categories, на который нет ссылки в таблице Parts.
В ряде случаев при соединениях таблиц составить менее громоздкие запросы можно с помощью предиката EXISTS и без использования JOIN.
Есть база данных "Театр". Таблица Play содержит данные о постановках. Таблица Team - о ролях актёров. Таблица Actor - об актёрах. Таблица Director - о режиссёрах. Поля таблиц, первичные и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).
Пример 3. Вывести список актеров, которые в одном спектакле играют более одной роли, и количество их ролей.
Оператор JOIN использовать 1 раз. Использовать HAVING, GROUP BY .
Подсказка. Оператор HAVING применяется к числу ролей, подсчитанных агрегатной функцией COUNT.
LEFT OUTER JOIN (левое внешнее соединение)
Запрос с оператором LEFT OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями из первой по порядку (левой) таблицы, даже если они не соответствуют условию. У записей левой таблицы, которые не соответствуют условию, значение столбца из правой таблицы будет NULL (неопределённым).
Пример 4. База данных и таблицы - те же, что и в примере 1.
Для получения результирующей таблицы, в которой данные из двух таблиц полностью пересекаются по условию и дополняются всеми данными из таблицы Parts, которые не соответствуют условию, пишем следующий запрос:
SELECT PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Price FROM PARTS LEFT OUTER JOIN CATEGORIES ON PARTS.Cat = CATEGORIES.Cat_ID
Результатом выполнения запроса будет следующая таблица:
| Part | Cat | Price |
| Квартиры | 505 | 210,00 |
| Автомашины | 205 | 160,00 |
| Доски | 10 | 105,00 |
| Шкафы | 30 | 77,00 |
| Книги | 160 | NULL |
В результирующей таблице, в отличие от таблицы из примера 1, есть Книги, но значение столбца Цены (Price) у них - NULL, так как эта запись имеет идентификатор категории, которой нет в таблице Categories.
RIGHT OUTER JOIN (правое внешнее соединение)
Запрос с оператором RIGHT OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями из второй по порядку (правой) таблицы, даже если они не соответствуют условию. У записей правой таблицы, которые не соответствуют условию, значение столбца из левой таблицы будет NULL (неопределённым).
Пример 5.
Для получения результирующей таблицы, в которой данные из двух таблиц полностью пересекаются по условию и дополняются всеми данными из таблицы Categories, которые не соответствуют условию, пишем следующий запрос:
SELECT PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Price FROM PARTS RIGHT OUTER JOIN CATEGORIES ON PARTS.Cat = CATEGORIES.Cat_ID
Результатом выполнения запроса будет следующая таблица:
| Part | Cat | Price |
| Квартиры | 505 | 210,00 |
| Автомашины | 205 | 160,00 |
| Доски | 10 | 105,00 |
| Шкафы | 30 | 77,00 |
| NULL | 45 | 65,00 |
В результирующей таблице, в отличие от таблицы из примера 1, есть запись с категорией 45 и ценой 65,00, но значение столбца Части (Part) у неё - NULL, так как эта запись имеет идентификатор категории, на которую нет ссылок в таблице Parts.
FULL OUTER JOIN (полное внешнее соединение)
Запрос с оператором FULL OUTER JOIN предназначен для соединения таблиц и вывода результирующей таблицы, в которой данные полностью пересекаются по условию, указанному после ON, и дополняются записями из первой (левой) и второй (правой) таблиц, даже если они не соответствуют условию. У записей, которые не соответствуют условию, значение столбцов из другой таблицы будет NULL (неопределённым).
Пример 6. База данных и таблицы - те же, что и в предыдущих примерах.
Для получения результирующей таблицы, в которой данные из двух таблиц полностью пересекаются по условию и дополняются всеми данными как из таблицы Parts, так и из таблицы Categories, которые не соответствуют условию, пишем следующий запрос:
SELECT PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Price FROM PARTS FULL OUTER JOIN CATEGORIES ON PARTS.Cat = CATEGORIES.Cat_ID
Результатом выполнения запроса будет следующая таблица:
| Part | Cat | Price |
| Квартиры | 505 | 210,00 |
| Автомашины | 205 | 160,00 |
| Доски | 10 | 105,00 |
| Шкафы | 30 | 77,00 |
| Книги | 160 | NULL |
| NULL | 45 | 65,00 |
В результирующей таблице есть записи Книги (из левой таблицы) и с категорией 45 (из правой таблицы), причём у первой из них неопределённая цена (столбец из правой таблицы), а у второй - неопределённая часть (столбец из левой таблицы).
Псевдонимы соединяемых таблиц
В предыдущих запросах мы указывали с названиями извлекаемых столбцов из разных таблиц полные имена этих таблиц. Такие запросы выглядят громоздко: одно и то же слово повторяется несколько раз. Нельзя ли как-то упростить конструкцию? Оказывается, можно. Для этого следует использовать псевдонимы таблиц - их сокращённые имена. Псевдоним может состоять и из одной буквы. Возможно любое количество букв в псевдониме, главное, чтобы запрос после сокращения был понятен Вам самим. Общее правило: в секции запроса, определяющей соединение, то есть вокруг слова JOIN нужно указать полные имена таблиц, а за каждым именем должен следовать псевдоним таблицы.
Пример 7. Переписать запрос из примера 1 с использованием псевдонимов соединяемых таблиц.
Запрос будет следующим:
SELECT P.Part, C.Cat_ID AS Cat, C.Price FROM PARTS P INNER JOIN CATEGORIES C ON P.Cat = C.Cat_ID
Запрос вернёт то же самое, что и запрос в примере 1, но он гораздо компактнее.
JOIN и соединение более двух таблиц
Реляционные базы данных должны подчиняться требованиям целостности и неизбыточности данных, в связи с чем данные об одном бизнес-процессе могут содержаться не только в одной, двух, но и в трёх и более таблицах. В этих случаях для анализа данных используются цепочки соединённых таблиц: например, в одной (первой) таблице содержится некоторый количественный показатель, вторую таблицу с первой и третьей связывают внешние ключи - данные пересекаются, но только третья таблица содержит условие, в зависимости от которого может быть выведен количественный показатель из первой таблицы. И таблиц может быть ещё больше. При помощи оператора SQL JOIN в одном запросе можно соединить большое число таблиц. В таких запросах за одной секцией соединения следует другая, причём каждый следующий JOIN соединяет со следующей таблицей таблицу, которая была второй в предыдущем звене цепочки. Таким образом, синтаксис SQL запроса для соединения более двух таблиц следующий:
SELECT ИМЕНА_СТОЛБЦОВ (1..N) FROM ИМЯ_ТАБЛИЦЫ_1 JOIN ИМЯ_ТАБЛИЦЫ_2 ON УСЛОВИЕ JOIN ИМЯ_ТАБЛИЦЫ_3 ON УСЛОВИЕ... JOIN ИМЯ_ТАБЛИЦЫ_M ON УСЛОВИЕ
Пример 8. База данных - та же, что и в предыдущих примерах. К таблицам Categories и Parts в этом примере добавится таблица Ads, содержащая данные об опубликованных на портале объявлениях. Приведём фрагмент таблицы Ads, в котором среди записей есть записи о тех объявлениях, срок публикации которых истекает 2018-04-02.
| A_Id | Part_ID | Date_start | Date_end | Text |
| 21 | 1 | "2018-02-11" | "2018-04-20" | "Продаю..." |
| 22 | 1 | "2018-02-11" | "2018-05-12" | "Продаю..." |
| ... | ... | ... | ... | ... |
| 27 | 1 | "2018-02-11" | "2018-04-02" | "Продаю..." |
| 28 | 2 | "2018-02-11" | "2018-04-21" | "Продаю..." |
| 29 | 2 | "2018-02-11" | "2018-04-02" | "Продаю..." |
| 30 | 3 | "2018-02-11" | "2018-04-22" | "Продаю..." |
| 31 | 4 | "2018-02-11" | "2018-05-02" | "Продаю..." |
| 32 | 4 | "2018-02-11" | "2018-04-13" | "Продаю..." |
| 33 | 3 | "2018-02-11" | "2018-04-12" | "Продаю..." |
| 34 | 4 | "2018-02-11" | "2018-04-23" | "Продаю..." |
Представим, что сегодня "2018-04-02", то есть это значение принимает функция CURDATE() - текущая дата . Требуется узнать, к каким категориям принадлежат объявления, срок публикации которых истекает сегодня. Названия категорий есть только в таблице CATEGORIES, а даты истечения срока публикации объявлений - только в таблице ADS. В таблице PARTS - части категорий (или проще, подкатегории) опубликованных объявлений. Но внешним ключом Cat_ID таблица PARTS связана с таблицей CATEGORIES, а таблица ADS связана внешним ключом Part_ID с таблицей PARTS. Поэтому соединяем в одном запросе три таблицы и этот запрос можно с максимальной корректностью назвать цепочкой.
Запрос будет следующим:
Результат запроса - таблица, содержащая названия двух категорий - "Недвижимость" и "Транспорт":
| Cat_name |
| Недвижимость |
| Транспорт |
CROSS JOIN (перекрестное соединение)
Использование оператора SQL CROSS JOIN в наиболее простой форме - без условия соединения - реализует операцию декартова произведения в реляционной алгебре . Результатом такого соединения будет сцепление каждой строки первой таблицы с каждой строкой второй таблицы. Таблицы могут быть записаны в запросе либо через оператор CROSS JOIN, либо через запятую между ними.
Пример 9. База данных - всё та же, таблицы - Categories и Parts. Реализовать операцию декартова произведения этих двух таблиц.
Запрос будет следующим:
SELECT (*) Categories CROSS JOIN Parts
Или без явного указания CROSS JOIN - через запятую:
SELECT (*) Categories , Parts
Запрос вернёт таблицу из 5 * 5 = 25 строк, фрагмент которой приведён ниже:
| Cat_ID | Cat_name | Price | Part_ID | Part | Cat |
| 10 | Стройматериалы | 105,00 | 1 | Квартиры | 505 |
| 10 | Стройматериалы | 105,00 | 2 | Автомашины | 205 |
| 10 | Стройматериалы | 105,00 | 3 | Доски | 10 |
| 10 | Стройматериалы | 105,00 | 4 | Шкафы | 30 |
| 10 | Стройматериалы | 105,00 | 5 | Книги | 160 |
| ... | ... | ... | ... | ... | ... |
| 45 | Техника | 65,00 | 1 | Квартиры | 505 |
| 45 | Техника | 65,00 | 2 | Автомашины | 205 |
| 45 | Техника | 65,00 | 3 | Доски | 10 |
| 45 | Техника | 65,00 | 4 | Шкафы | 30 |
| 45 | Техника | 65,00 | 5 | Книги | 160 |
Как видно из примера, если результат такого запроса и имеет какую-либо ценность, то это, возможно, наглядная ценность в некоторых случаях, когда не требуется вывести структурированную информацию, тем более, даже самую простейшую аналитическую выборку. Кстати, можно указать выводимые столбцы из каждой таблицы, но и тогда информационная ценность такого запроса не повысится.
Но для CROSS JOIN можно задать условие соединения! Результат будет совсем иным. При использовании оператора "запятая" вместо явного указания CROSS JOIN условие соединения задаётся не словом ON, а словом WHERE.
Пример 10. Та же база данных портала объявлений, таблицы Categories и Parts. Используя перекрестное соединение, соединить таблицы так, чтобы данные полностью пересекались по условию. Условие - совпадение идентификатора категории в таблице Categories и ссылки на категорию в таблице Parts.
Запрос будет следующим:
Запрос вернёт то же самое, что и запрос в примере 1:
| Part | Cat | Price |
| Квартиры | 505 | 210,00 |
| Автомашины | 205 | 160,00 |
| Доски | 10 | 105,00 |
| Шкафы | 30 | 77,00 |
И это совпадение не случайно. Запрос c перекрестным соединением по условию соединения полностью аналогичен запросу с внутренним соединением - INNER JOIN - или, учитывая, что слово INNER - не обязательное, просто JOIN.
Таким образом, какой вариант запроса использовать - вопрос стиля или даже привычки специалиста по работе с базой данных. Возможно, перекрёстное соединение с условием для двух таблиц может представляться более компактным. Но преимущество перекрестного соединения для более чем двух таблиц (это также возможно) весьма спорно. В этом случае WHERE-условия пересечения перечисляются через слово AND. Такая конструкция может быть громоздкой и трудной для чтения, если в конце запроса есть также секция WHERE с условиями выборки.
Реляционные базы данных и язык SQL
Операция соединения, как и другие бинарные операции, предназначена для обеспечения выборки данных из двух таблиц и включения этих данных в один результирующий набор. Отличительной особенностью операции соединения является следующее:
- в схему таблицы-результата входят столбцы обеих исходных таблиц (таблиц-операндов), то есть схема результата является «сцеплением» схем операндов;
- каждая строка таблицы-результата является «сцеплением» строки из одной таблицы-операнда со строкой второй таблицы-операнда.
Определение того, какие именно исходные строки войдут в результат и в каких сочетаниях, зависит от типа операции соединения и от явно заданного условия соединения . Условие соединения, то есть условие сопоставления строк исходных таблиц друг с другом, представляет собой логическое выражение (предикат).
При необходимости соединения не двух, а нескольких таблиц, операция соединения применяется несколько раз (последовательно).
Описание оператора
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 | Эдгар Кодд Реляционная база данных |
Разработка любой базы данных подразумевает не только создание и наполнение таблиц разнообразной информацией, но и дальнейшую работу с данными. Для корректного выполнения разнообразных задач по выбору данных из таблиц и формированию отчетов, используется стандартная конструкция Select.
Выборки данных из таблиц
Если рассматривать задачу выбора данных или построения некоторого отчета, можно определить уровень сложности данной операции. Как правило, при работе с серьезными (по объему информации) базами данных, которые формируются, например, в интернет-магазинах или крупных компаниях, выборка данных не будет ограничиваться лишь одной таблицей. Как правило, выборки могут быть из довольно большого количества не только связанных между собой таблиц, но и вложенных запросов/подзапросов, которые составляет сам программист, в зависимости от поставленной перед ним задачи. Для выборки из одной таблицы можно использовать простейшую конструкцию:
| Select * from Person |
где Person - имя таблицы, из которой необходимо сделать выборку данных.
Если же будет необходимость выбрать данные из нескольких таблиц, можно использовать одну из стандартных конструкций для объединения нескольких таблиц.
Способы подключения дополнительных таблиц
Если рассматривать использование такого рода конструкций на начальном уровне, то можно выделить следующие механизмы подключения необходимого количества таблиц для выборки, а именно:
- Оператор Inner Join.
- Left Join или, это второй способ записи, Left Outer Join.
- Cross Join.
- Full Join.
Использование операторов объединения таблиц на практике можно усвоить, рассмотрев применение оператора SQL - Inner Join. Пример его использования будет выглядеть следующим образом:
Select * from Person |
Язык SQL и оператор Join Inner Join можно использовать не только для объединения двух и более таблиц, но и для подключения иных подзапросов, что значительно облегчает работу администраторов базы данных и, как правило, может значительно ускорить выполнение определенных, сложных по структуре запросов.
Объединение данных в таблицах построчно
Если рассматривать подключение большого количества подзапросов и сборку данных в единую таблицу строка за строкой, то можно использовать также операторы Union, и Union All.
Применение этих конструкций будет зависеть от поставленной перед разработчиком задачи и результата, которого он хочет достичь в итоге.
Описание оператора Inner Join
В большинстве случаев для объединения нескольких таблиц в языке SQL используется оператор Inner Join. Описание Inner Join в SQL довольно простое для понимания среднестатистического программиста, который только начинает разбираться в базах данных. Если рассмотреть описание механизма работы этой конструкции, то получим следующую картину. Логика оператора в целом построена на возможности пересечения и выборки только тех данных, которые есть в каждой из входящих в запрос таблиц.
Если рассмотреть такую работу с точки зрения графической интерпретации, то получим структуру оператора SQL Inner Join, пример которой можно показать с помощью следующей схемы:
К примеру, мы имеем две таблицы, схема которых показана на рисунке. Они в свою очередь, имеют разное количество записей. В каждой из таблиц есть поля, которые связаны между собой. Если попытаться пояснить работу оператора исходя из рисунка, то возвращаемый результат будет в виде набора записей из двух таблиц, где номера связанных между собой полей совпадают. Проще говоря, запрос вернет только те записи (из таблицы номер два), данные о которых есть в таблице номер один.
Синтаксис оператора Inner Join
Как уже говорилось ранее, оператор Inner Join, а именно его синтаксис, необычайно прост. Для организации связей между таблицами в пределах одной выборки достаточно будет запомнить и использовать следующую принципиальную схему построения оператора, которая прописывается в одну строчку программного SQL-кода, а именно:
- Inner Join [Имя таблицы] on [ключевое поле из таблицы, к которой подключаем] = [Ключевому полю подключаемой таблицы].
Для связи в данном операторе используются главные ключи таблиц. Как правило, в группе таблиц, которые хранят информацию о сотрудниках, ранее описанные Person и Subdivision имеют хотя бы по одной похожей записи. Итак, рассмотрим подробнее Inner Join, пример которого был показан несколько ранее.
Пример и описание подключения к выборке одной таблицы
У нас есть таблица Person, где хранится информация обо всех сотрудниках, работающих в компании. Сразу отметим, что главным ключем данной таблицы является поле - Pe_ID. Как раз по нему и будет идти связка.
Вторая таблица Subdivision будет хранить информацию о подразделениях, в которых работают сотрудники. Она, в свою очередь, связана с помощью поля Su_Person с таблицей Person. О чем это говорит? Исходя из схемы данных можно сказать, что в таблице подразделений для каждой записи из таблицы «Сотрудники» будет информация об отделе, в котором они работают. Именно по этой связи и будет работать оператор Inner Join.
Для более понятного использования рассмотрим оператор SQL Inner Join (примеры его использования для одной и двух таблиц). Если рассматривать пример для одной таблицы, то тут все довольно просто:
Select * from Person Inner join Subdivision on Su_Person = Pe_ID |
Пример подключения двух таблиц и подзапроса
Оператор SQL Inner Join, примеры использования которого для выборки данных из нескольких таблиц можно организовать вышеуказанным образом, работает по чуть усложненному принципу. Для двух таблиц усложним задачу. Скажем, у нас есть таблица Depart, в которой хранится информация обо всех отделах в каждом из подразделений. В в эту таблицу записан номер подразделения и номер сотрудника и нужно дополнить выборку данных названием каждого отдела. Забегая вперед, стоит сказать, что для решения этой задачи можно воспользоваться двумя методами.
Первый способ заключается в подключении таблицы отделов к выборке. Организовать запрос в этом случае можно таким образом:
Стоит отметить, что такая конструкция не всегда может ускорить работу запроса. Иногда бывают случаи, когда приходится использовать дополнительно выборку данных во временную таблицу (если их объем слишком большой), а потом ее объединять с основной выборкой.
Пример использования оператора Inner Join для выборок из большого количества таблиц
Построение сложных запросов подразумевает использование для выборки данных значительного количества таблиц и подзапросов, связанных между собой. Этим требованиям может удовлетворить SQL Inner Join синтаксис. Примеры использования оператора в данном случаем могут усложняться не только выборками из многих мест хранения данных, но и с большого количества вложенных подзапросов. Для конкретного примера можно взять выборку данных из системных таблиц (оператор Inner Join SQL). Пример - 3 таблицы - в этом случае будет иметь довольно сложную структуру.
В данном случае подключено (к основной таблице) еще три дополнительно и введено несколько условий выбора данных.
При использовании оператора Inner 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 обычно опускается.