Язык SQL – объединение JOIN. Оператор соединения 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 строк.

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

This Oracle tutorial explains how to use JOINS (inner and outer) in Oracle with syntax, visual illustrations, and examples.

Description

Oracle JOINS are used to retrieve data from multiple tables. An Oracle JOIN is performed whenever two or more tables are joined in a SQL statement.

There are 4 different types of Oracle joins:

• Oracle INNER JOIN (or sometimes called simple join)
• Oracle LEFT OUTER JOIN (or sometimes called LEFT JOIN)
• Oracle RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
• Oracle FULL OUTER JOIN (or sometimes called FULL JOIN)

So let"s discuss Oracle JOIN syntax, look at visual illustrations of Oracle JOINS, and explore Oracle JOIN examples.

INNER JOIN (simple join)

Chances are, you"ve already written a statement that uses an Oracle INNER JOIN. It is the most common type of join. Oracle INNER JOINS return all rows from multiple tables where the join condition is met.

Syntax

The syntax for the INNER JOIN in Oracle/PLSQL is:

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

Visual Illustration

In this visual diagram, the Oracle INNER JOIN returns the shaded area:

The Oracle INNER JOIN would return the records where table1 and table2 intersect.

Example

Here is an example of an Oracle INNER JOIN:

This Oracle INNER JOIN example would return all rows from the suppliers and orders tables where there is a matching supplier_id value in both the suppliers and orders tables.

Let"s look at some data to explain how the INNER JOINS work:

We have a table called suppliers

We have another table called orders

If we run the Oracle SELECT statement (that contains an INNER JOIN) below:

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

The rows for Microsoft and NVIDIA from the supplier table would be omitted, since the supplier_id"s 10002 and 10003 do not exist in both tables. The row for 500127 (order_id) from the orders table would be omitted, since the supplier_id 10004 does not exist in the suppliers table.

Old Syntax

As a final note, it is worth mentioning that the Oracle INNER JOIN example above could be rewritten using the older implicit syntax as follows (but we still recommend using the INNER JOIN keyword syntax):

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers, orders WHERE suppliers.supplier_id = orders.supplier_id;

LEFT OUTER JOIN

Another type of join is called an Oracle LEFT OUTER JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition and only

Syntax

The syntax for the Oracle LEFT OUTER JOIN is:

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

In some databases, the LEFT OUTER JOIN keywords are replaced with LEFT JOIN.

Visual Illustration

In this visual diagram, the Oracle LEFT OUTER JOIN returns the shaded area:

The Oracle LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1 .

Example

Here is an example of an Oracle LEFT OUTER JOIN:

This LEFT OUTER JOIN example would return all rows from the suppliers table and only those rows from the orders table where the joined fields are equal.

> in the result set.

Let"s look at some data to explain how LEFT OUTER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:

orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

If we run the SELECT statement (that contains a LEFT OUTER JOIN) below:

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

Our result set would look like this:

The rows for Microsoft and NVIDIA would be included because a LEFT OUTER JOIN was used. However, you will notice that the order_date field for those records contains a value.

Old Syntax

As a final note, it is worth mentioning that the LEFT OUTER JOIN example above could be rewritten using the older implicit syntax that utilizes the outer join operator (+) as follows (but we still recommend using the LEFT OUTER JOIN keyword syntax):

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers, orders WHERE suppliers.supplier_id = orders.supplier_id(+);

RIGHT OUTER JOIN

Another type of join is called an Oracle RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax

The syntax for the Oracle RIGHT OUTER JOIN is:

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

In some databases, the RIGHT OUTER JOIN keywords are replaced with RIGHT JOIN.

Visual Illustration

In this visual diagram, the Oracle RIGHT OUTER JOIN returns the shaded area:

The Oracle RIGHT OUTER JOIN would return the all records from table2 and only those records from table1 that intersect with table2 .

Example

Here is an example of an Oracle RIGHT OUTER JOIN:

This RIGHT OUTER JOIN example would return all rows from the orders table and only those rows from the suppliers table where the joined fields are equal.

If a supplier_id value in the orders table does not exist in the suppliers table, all fields in the suppliers table will display as > in the result set.

Let"s look at some data to explain how RIGHT OUTER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:

We have a second table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

If we run the SELECT statement (that contains a RIGHT OUTER JOIN) below:

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

Our result set would look like this:

The row for 500127 (order_id) would be included because a RIGHT OUTER JOIN was used. However, you will notice that the supplier_name field for that record contains a value.

Old Syntax

As a final note, it is worth mentioning that the RIGHT OUTER JOIN example above could be rewritten using the older implicit syntax that utilizes the outer join operator (+) as follows (but we still recommend using the RIGHT OUTER JOIN keyword syntax):

SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers, orders WHERE suppliers.supplier_id(+) = orders.supplier_id;

FULL OUTER JOIN

Another type of join is called an Oracle FULL OUTER JOIN. This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.

Syntax

The syntax for the Oracle FULL OUTER JOIN is:

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

In some databases, the FULL OUTER JOIN keywords are replaced with FULL JOIN.

Visual Illustration

In this visual diagram, the Oracle FULL OUTER JOIN returns the shaded area:

The Oracle FULL OUTER JOIN would return the all records from both table1 and table2 .

Example

Here is an example of an Oracle FULL OUTER JOIN:

This FULL OUTER JOIN example would return all rows from the suppliers table and all rows from the orders table and whenever the join condition is not met, would be extended to those fields in the result set.

If a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as > in the result set. If a supplier_id value in the orders table does not exist in the suppliers table, all fields in the suppliers table will display as in the result set.

Let"s look at some data to explain how FULL OUTER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:

We have a second table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

If we run the SELECT statement (that contains a FULL OUTER JOIN) below:

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

Our result set would look like this:

The rows for Microsoft and NVIDIA would be included because a FULL OUTER JOIN was used. However, you will notice that the order_date field for those records contains a value.

The row for supplier_id 10004 would be also included because a FULL OUTER JOIN was used. However, you will notice that the supplier_id and supplier_name field for those records contain a value.

Old Syntax

As a final note, it is worth mentioning that the FULL OUTER JOIN example above could not have been written in the old syntax without using a

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

Оператор языка 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

В результирующей таблице нет Книг, так как эта запись ссылается на категорию, которой нет в таблице 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

Результатом выполнения запроса будет следующая таблица:

В результирующей таблице, в отличие от таблицы из примера 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

Результатом выполнения запроса будет следующая таблица:

В результирующей таблице, в отличие от таблицы из примера 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

Результатом выполнения запроса будет следующая таблица:

В результирующей таблице есть записи Книги (из левой таблицы) и с категорией 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.

Представим, что сегодня "2018-04-02", то есть это значение принимает функция CURDATE() - текущая дата . Требуется узнать, к каким категориям принадлежат объявления, срок публикации которых истекает сегодня. Названия категорий есть только в таблице CATEGORIES, а даты истечения срока публикации объявлений - только в таблице ADS. В таблице PARTS - части категорий (или проще, подкатегории) опубликованных объявлений. Но внешним ключом Cat_ID таблица PARTS связана с таблицей CATEGORIES, а таблица ADS связана внешним ключом Part_ID с таблицей PARTS. Поэтому соединяем в одном запросе три таблицы и этот запрос можно с максимальной корректностью назвать цепочкой.

Запрос будет следующим:

Результат запроса - таблица, содержащая названия двух категорий - "Недвижимость" и "Транспорт":

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

Использование оператора SQL CROSS JOIN в наиболее простой форме - без условия соединения - реализует операцию декартова произведения в реляционной алгебре . Результатом такого соединения будет сцепление каждой строки первой таблицы с каждой строкой второй таблицы. Таблицы могут быть записаны в запросе либо через оператор CROSS JOIN, либо через запятую между ними.

Пример 9. База данных - всё та же, таблицы - Categories и Parts. Реализовать операцию декартова произведения этих двух таблиц.

Запрос будет следующим:

SELECT (*) Categories CROSS JOIN Parts

Или без явного указания CROSS JOIN - через запятую:

SELECT (*) Categories , Parts

Запрос вернёт таблицу из 5 * 5 = 25 строк, фрагмент которой приведён ниже:

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

Но для CROSS JOIN можно задать условие соединения! Результат будет совсем иным. При использовании оператора "запятая" вместо явного указания CROSS JOIN условие соединения задаётся не словом ON, а словом WHERE.

Пример 10. Та же база данных портала объявлений, таблицы Categories и Parts. Используя перекрестное соединение, соединить таблицы так, чтобы данные полностью пересекались по условию. Условие - совпадение идентификатора категории в таблице Categories и ссылки на категорию в таблице Parts.

Запрос будет следующим:

Запрос вернёт то же самое, что и запрос в примере 1:

И это совпадение не случайно. Запрос c перекрестным соединением по условию соединения полностью аналогичен запросу с внутренним соединением - INNER JOIN - или, учитывая, что слово INNER - не обязательное, просто JOIN.

Таким образом, какой вариант запроса использовать - вопрос стиля или даже привычки специалиста по работе с базой данных. Возможно, перекрёстное соединение с условием для двух таблиц может представляться более компактным. Но преимущество перекрестного соединения для более чем двух таблиц (это также возможно) весьма спорно. В этом случае WHERE-условия пересечения перечисляются через слово AND. Такая конструкция может быть громоздкой и трудной для чтения, если в конце запроса есть также секция WHERE с условиями выборки.

Реляционные базы данных и язык SQL