JOINS — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном материале вы узнаете, как использовать JOINS (INNER и OUTER) в Oracle с синтаксисом и примерами.

Описание

Oracle JOINS используются для извлечения данных из нескольких таблиц. JOIN выполняется всякий раз, когда две или более таблиц объединяются в SQL предложении.

Есть 4 различных типа присоединения Oracle:

Oracle INNER JOIN (или иногда называют простое соединение)
Oracle LEFT OUTER JOIN (или иногда называют LEFT JOIN)
Oracle RIGHT OUTER JOIN (или иногда называют RIGHT JOIN)
Oracle FULL OUTER JOIN (или иногда называют FULL JOIN)

Рассмотрим синтаксис Oracle JOIN, а также изучим примеры Oracle JOIN.

INNER JOIN (простое соединение)

Скорее всего, вы уже писали запросы в которых используются Oracle INNER JOIN. Это наиболее распространенный тип соединения. Oracle INNER JOINS возвращает все строки из нескольких таблиц, где выполняется условия соединения.

Синтаксис

Синтаксис INNER JOIN в Oracle/PLSQL:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

В этом рисунке, Oracle INNER JOIN возвращает затененную область:

Oracle INNER JOIN будет возвращать записи, где table1 и table2 будут пересекаться.

Пример

Ниже приведен пример Oracle INNER JOIN:

SELECT suppliers.supplier_id, 
       suppliers.supplier_name, 
       orders.order_date
  FROM suppliers
 INNER JOIN orders
    ON suppliers.supplier_id = orders.supplier_id;

SELECT suppliers.supplier_id,

suppliers.supplier_name,

orders.order_date

FROM suppliers

INNER JOIN orders

ON suppliers.supplier_id = orders.supplier_id;

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

SELECT suppliers.supplier_id, 
       suppliers.supplier_name, 
       orders.order_date
  FROM suppliers
 INNER JOIN orders
    ON suppliers.supplier_id = orders.supplier_id;

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	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 columns
FROM 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.

Пример

SELECT suppliers.supplier_id, 
       suppliers.supplier_name, 
       orders.order_date
  FROM suppliers
  LEFT OUTER JOIN orders
    ON suppliers.supplier_id = orders.supplier_id;

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) которая содержит следующие данные:

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) ниже:

SELECT suppliers.supplier_id, 
       suppliers.supplier_name, 
       orders.order_date
  FROM suppliers
  LEFT OUTER JOIN orders
    ON suppliers.supplier_id = orders.supplier_id;

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	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 columns
FROM 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:

SELECT orders.order_id, 
       orders.order_date, 
       suppliers.supplier_name
  FROM suppliers
 RIGHT OUTER JOIN orders
    ON suppliers.supplier_id = orders.supplier_id;

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) которая содержит следующие данные:

supplier_id	supplier_name
10000	Apple
10001	Google

У нас есть вторая таблица 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) ниже:

SELECT orders.order_id, 
       orders.order_date, 
       suppliers.supplier_name
  FROM suppliers
 RIGHT OUTER JOIN orders
    ON suppliers.supplier_id = orders.supplier_id;

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	12.05.2016	Apple
500126	14.05.2016	Google
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 columns
FROM 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:

SELECT suppliers.supplier_id, 
       suppliers.supplier_name, 
       orders.order_date
  FROM suppliers
  FULL OUTER JOIN orders
    ON suppliers.supplier_id = orders.supplier_id;

SELECT suppliers.supplier_id,

suppliers.supplier_name,

orders.order_date

FROM suppliers

FULL OUTER JOIN orders

ON suppliers.supplier_id = orders.supplier_id;

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

SELECT suppliers.supplier_id, 
       suppliers.supplier_name, 
       orders.order_date
  FROM suppliers
  FULL OUTER JOIN orders
    ON suppliers.supplier_id = orders.supplier_id;

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	12.05.2016
10001	Hewlett Packard	14.05.2016
10002	Microsoft	null
10003	NVIDIA	null
null	null	18.05.2016

Строки для Microsoft и NVIDIA будут включены, так как используется FULL OUTER JOIN. Тем не менее, вы заметите, что поле order_date для этих записей содержит значение NULL.

Строка для supplier_id 10004 также будет включена, так как используется FULL OUTER JOIN. Тем не менее, вы заметите, что supplier_id и поле supplier_name для этих записей содержат значение NULL.