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

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

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

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

SELECT field1, field1, [,... n]
  FROM  Table1 t1
  {INNER | {LEFT | RIGHT | FULL} OUTER | CROSS } JOIN
  Table2 {ON <condition> | 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   <null>

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

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

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

select u.name as owner, a.name as auto
  from users u 
  left join autos a ON a.oid = u.id
    where a.name is null

-- Результат запроса

owner   auto
ostap   <null>

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

Внешнее соединение OUTER JOIN с условием

Оператор перекрёстного соединения, 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 строк.