Inner join как работает. Язык SQL – объединение JOIN. Другие формы операций соединения

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

При необходимости объединении нескольких таблиц операция соединения должна применяться последовательно несколько раз.

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

Синтаксис подключения к запросу дополнительной таблицы с использованием оператора JOIN можно представить в следующем виде:

SELECT field1, field1, [,... n] FROM Table1 t1 {INNER | {LEFT | RIGHT | FULL} OUTER | CROSS } JOIN Table2 {ON | USING (field_name [,... n])}

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

Если названия столбцов, по которым происходит соединение таблиц, совпадают, то вместо ON можно использовать USING . Для оператора CROSS JOIN условие не указывается.

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

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

Таблица пользователей

CREATE TABLE users (id int not null, name varchar(32) not null, primary key (id)); -- Вставим в таблицу несколько записей insert into users (id, name) values (1, "alex"); insert into users (id, name) values (2, "piter"); insert into users (id, name) values (3, "serg"); insert into users (id, name) values (4, "olga"); insert into 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 into autos (id, oid, name) values (1, 1, "toyota camry"); insert into autos (id, oid, name) values (2, 1, "toyota prado"); insert into autos (id, oid, name) values (3, 2, "renault megane"); insert into autos (id, oid, name) values (4, 3, "nissan x-trail"); insert into autos (id, oid, name) values (5, 4, "suzuki swift"); insert into autos (id, oid, name) values (6, 4, "suzuki vitara");

Внутреннее соединение, INNER JOIN

INNER JOIN - это оператор внутреннего соединения двух таблиц. Он является симметричным, поэтому порядок таблиц для оператора неважен.

Применяется INNER JOIN для получения только тех строк, для которых существует соответствие записей в главной и присоединяемой таблице. Алгоритм формирования результата: каждая строка главной таблицы сопоставляется с каждой строкой присоединяемой таблицы. После этого проверяется условие соединения. Если условие истинно, в результирующий набор добавляется соответствующая «соединённая» строка.

Запрос с выбором колонок результирующего набора select u.name as owner, a.name as auto from users u inner join autos a ON a.oid = u.id -- Результат запроса owner auto alex toyota camry alex toyota prado piter renault megane serg nissan x-trail olga suzuki swift olga suzuki vitara -- Запрос без выбора колонок результирующего набора select * from users u inner join autos a ON a.oid = u.id -- Результат запроса id name id1 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

Select u.name as owner, a.name as auto from users u left join autos a ON a.oid = u.id -- Результат запроса owner auto alex toyota camry alex toyota prado piter renault megane serg nissan x-trail olga suzuki swift olga suzuki vitara ostap

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

Оператор LEFT OUTER JOIN с фильтрацией

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

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

Оператор перекрёстного соединения, CROSS JOIN

CROSS JOIN - это оператор перекрёстного соединения (декартово произведение). Оператор является симметричным и порядок таблиц для оператора неважен.

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

Select * from 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 1 alex 2 toyota 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 строк.

Продолжаем изучать основы SQL , и пришло время поговорить о простых объединениях JOIN. И сегодня мы рассмотрим, как объединяются данные по средствам операторов LEFT JOIN, RIGHT JOIN, CROSS JOIN и INNER JOIN , другими словами, научимся писать запросы, которые объединяют данные, и как обычно изучать все это будем на примерах.

Объединения JOIN очень важны в SQL, так как без умения писать запросы с объединением данных разных объектов, просто не обойтись программисту SQL, да и просто админу который время от времени выгружает какие-то данные из базы данных, поэтому это относится к основам SQL и каждый человек, который имеет дело с SQL, должен иметь представление, что это такое.

Примечание! Все примеры будем писать в Management Studio SQL Server 2008.

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

И много другого, даже уже рассматривали объединения union и union all , но, так или иначе, более подробно именно об объединениях join мы с Вами не разговаривали, поэтому сегодня мы восполним этот пробел в наших знаниях.

И начнем мы как обычно с небольшой теории.

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

Объединение SQL LEFT и RIGHT JOIN

LEFT JOIN – это объединение данных по левому ключу, т.е. допустим, мы объединяем две таблицы по left join, и это значит что все данные из второй таблицы подтянутся к первой, а в случае отсутствия ключа выведется NULL значения, другими словами выведутся все данные из левой таблицы и все данные по ключу из правой таблицы.

RIGHT JOIN – это такое же объединение как и Left join только будут выводиться все данные из правой таблицы и только те данные из левой таблицы в которых есть ключ объединения.

Теперь давайте рассматривать примеры, и для начала создадим две таблицы:

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

Вот такие простенькие таблицы, И я для примера заполнил их вот такими данными:

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

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

Как видите, здесь данные из таблицы t1 вывелись все, а данные из таблицы t2 не все, так как строки с number = 4 там нет, поэтому и вывелись NULL значения.

А что будет, если бы мы объединяли по средствам right join, а было бы вот это:

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

Другими словами, вывелись все строки из таблицы t2 и соответствующие записи из таблицы t1, так как все те ключи, которые есть в таблице t2, есть и в таблице t1, и поэтому у нас нет NULL значений.

Объединение SQL INNER JOIN

Inner join – это объединение когда выводятся все записи из одной таблицы и все соответствующие записи из другой таблице, а те записи которых нет в одной или в другой таблице выводиться не будут, т.е. только те записи которые соответствуют ключу. Кстати сразу скажу, что inner join это то же самое, что и просто join без Inner. Пример:

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

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

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

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

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

И давайте напишем запрос с объединением по двум ключам:

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

И результат будет таким же, как и в предыдущем примере:

Но если мы, допустим во второй таблице в одной строке изменим, поле number2 на значение скажем 2, то результат будет уже совсем другой.

UPDATE test_table_2 set number2 = 2 WHERE number=1

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

Как видите, по второму ключу у нас одна строка не вывелась.

Объединение SQL CROSS JOIN

CROSS JOIN – это объединение SQL по которым каждая строка одной таблицы объединяется с каждой строкой другой таблицы. Лично у меня это объединение редко требуется, но все равно иногда требуется, поэтому Вы также должны уметь его использовать. Например, в нашем случае получится, конечно, не понятно что, но все равно давайте попробуем, тем более синтаксис немного отличается:

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

Здесь у нас каждой строке таблицы test_table соответствует каждая строка из таблицы test_table_2, т.е. в таблице test_table у нас 4 строки, а в таблице test_table_2 3 строки 4 умножить 3 и будет 12, как и у нас вывелось 12 строк.

И напоследок, давайте покажу, как можно объединять несколько таблиц, для этого я, просто для примера, несколько раз объединю нашу первую таблицу со второй, смысла в объединение в данном случае, конечно, нет но, Вы увидите, как можно это делать и так приступим:

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

Как видите, я здесь объединяю и по left и по right и по inner просто, для того чтобы это было наглядно.

С объединениями я думаю достаточно, тем более ничего сложного в них нет. Но на этом изучение SQL не закончено в следующих статьях мы продолжим, а пока тренируйтесь и пишите свои запросы. Удачи!

Это учебное пособие 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

Этот пример 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

Этот пример 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

Этот пример 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 .

Операция соединения, как и другие бинарные операции, предназначена для обеспечения выборки данных из двух таблиц и включения этих данных в один результирующий набор. Отличительной особенностью операции соединения является следующее:

• в схему таблицы-результата входят столбцы обеих исходных таблиц (таблиц-операндов), то есть схема результата является «сцеплением» схем операндов;
• каждая строка таблицы-результата является «сцеплением» строки из одной таблицы-операнда со строкой второй таблицы-операнда.

Определение того, какие именно исходные строки войдут в результат и в каких сочетаниях, зависит от типа операции соединения и от явно заданного условия соединения . Условие соединения, то есть условие сопоставления строк исходных таблиц друг с другом, представляет собой логическое выражение (предикат).

При необходимости соединения не двух, а нескольких таблиц, операция соединения применяется несколько раз (последовательно).

Описание оператора

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

Результат:

OUTER JOIN

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

LEFT OUTER JOIN

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

Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.

Тело p .

1. p .
2. Затем в результат добавляются те записи левой таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие правой таблице, заполняются значениями NULL .

SELECT * FROM Person LEFT OUTER JOIN City ON Person. CityId = City. Id

Результат:

RIGHT OUTER JOIN

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

Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.

Тело результата логически формируется следующим образом. Пусть выполняется соединение левой и правой таблиц по предикату (условию) p .

1. В результат включается внутреннее соединение (INNER JOIN) левой и правой таблиц по предикату p .
2. Затем в результат добавляются те записи правой таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие левой таблице, заполняются значениями NULL .

SELECT * FROM Person RIGHT OUTER JOIN City ON Person. CityId = City. Id

Результат:

FULL OUTER JOIN

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

Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.

Тело результата логически формируется следующим образом. Пусть выполняется соединение первой и второй таблиц по предикату (условию) p . Слова «первой» и «второй» здесь не обозначают порядок в записи (который неважен), а используются лишь для различения таблиц.

1. В результат включается внутреннее соединение (INNER JOIN) первой и второй таблиц по предикату p .
2. В результат добавляются те записи первой таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие второй таблице, заполняются значениями NULL .
3. В результат добавляются те записи второй таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие первой таблице, заполняются значениями NULL .

SELECT * FROM Person FULL OUTER JOIN City ON Person. CityId = City. Id

Результат:

CROSS JOIN

Оператор перекрёстного соединения , или декартова произведения CROSS JOIN соединяет две таблицы. Порядок таблиц для оператора неважен, поскольку оператор является симметричным .

Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.

Тело результата логически формируется следующим образом. Каждая строка одной таблицы соединяется с каждой строкой второй таблицы, давая тем самым в результате все возможные сочетания строк двух таблиц.

SELECT * FROM Person CROSS JOIN City

SELECT * FROM Person, City

Результат:

Если в предложении WHERE добавить условие соединения, то есть ограничения на сочетания кортежей, то результат эквивалентен операции INNER JOIN с таким же условием:

Таким образом, CROSS JOIN + WHERE(предикат ) и INNER JOIN(предикат ) синтаксически являются альтернативными формами записи одной и той же логической операции внутреннего соединения. Синтаксис CROSS JOIN + WHERE для операции соединения называют устаревшим, поскольку его не рекомендует стандарт SQL ANSI.

Примечания

Ссылки

• Описание видов Join в Oracle (рус.)
• Jeff Atwood, A Visual Explanation of SQL Joins (англ.)

В 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 обычно опускается.