Для рассмотрения примеров я буду использовать внутренние представления (f1, f2) объединенные во фразе with.
Итак, левое полуоткрытое соединение. Дополнение правой таблицы отсутствующих сравниваемых значений пустыми значениями (NULL), все значения правой таблицы, которые отсутствуют в левой таблице отбрасываются
with
f1( a, b ) as (
select 1 as a, 'type 1' as b from dual
union all
select a + 1, b
from f1
where a < 10
),
f2( a, b ) as (
select 5 as a, 'type 2' as b from dual
union all
select a + 1, b
from f2
where a < 15
)
select f1.a, f1.b, f2.a, f2.b
from f1 left outer join f2
on f1.a = f2.a
order by f1.a nulls first
данный запрос в версиях ниже 9 записывается немного иначе
with
f1( a, b ) as (
select 1 as a, 'type 1' as b from dual
union all
select a + 1, b
from f1
where a < 10
),
f2( a, b ) as (
select 5 as a, 'type 2' as b from dual
union all
select a + 1, b
from f2
where a < 15
)
select f1.a, f1.b, f2.a, f2.b
from f1, f2
where f1.a = f2.a (+)
order by f1.a nulls first
Правое полуоткрытое соединение. Дополнение левой таблицы отсутствующих сравниваемых значений пустыми значениями (NULL), все значения левой таблицы, которые отсутствуют в правой таблице отбрасываются
with
f1( a, b ) as (
select 1 as a, 'type 1' as b from dual
union all
select a + 1, b
from f1
where a < 10
),
f2( a, b ) as (
select 5 as a, 'type 2' as b from dual
union all
select a + 1, b
from f2
where a < 15
)
select f1.a, f1.b, f2.a, f2.b
from f1 right outer join f2
on f1.a = f2.a
order by f1.a nulls first
на старый лад (версия ниже 9)
with
f1( a, b ) as (
select 1 as a, 'type 1' as b from dual
union all
select a + 1, b
from f1
where a < 10
),
f2( a, b ) as (
select 5 as a, 'type 2' as b from dual
union all
select a + 1, b
from f2
where a < 15
)
select f1.a, f1.b, f2.a, f2.b
from f1, f2
where f1.a (+) = f2.a
order by f1.a nulls first
Полное открытое соединение. Дополнение отсутствующих сравниваемых значений пустыми значениями (NULL) и в левой, и в правой таблицах. Никакие значения таблиц не теряются
with
f1( a, b ) as (
select 1 as a, 'type 1' as b from dual
union all
select a + 1, b
from f1
where a < 10
),
f2( a, b ) as (
select 5 as a, 'type 2' as b from dual
union all
select a + 1, b
from f2
where a < 15
)
select f1.a, f1.b, f2.a, f2.b
from f1 full outer join f2
on f1.a = f2.a
order by f1.a nulls last
Внутреннее закрытое соединение. По результату схоже с естественным соединением
with
f1( a, b ) as (
select 1 as a, 'type 1' as b from dual
union all
select a + 1, b
from f1
where a < 10
),
f2( a, b ) as (
select 5 as a, 'type 2' as b from dual
union all
select a + 1, b
from f2
where a < 15
)
select f1.a, f1.b, f2.a, f2.b
from f1 inner join f2
on f1.a = f2.a
order by f1.a nulls last
Получение декартова произведения таблиц
with
f1( a, b ) as (
select 1 as a, 'type 1' as b from dual
union all
select a + 1, b
from f1
where a < 10
),
f2( a, b ) as (
select 5 as a, 'type 2' as b from dual
union all
select a + 1, b
from f2
where a < 15
)
select f1.a, f1.b, f2.a, f2.b
from f1, f2
или (более предпочтительно)
with
f1( a, b ) as (
select 1 as a, 'type 1' as b from dual
union all
select a + 1, b
from f1
where a < 10
),
f2( a, b ) as (
select 5 as a, 'type 2' as b from dual
union all
select a + 1, b
from f2
where a < 15
)
select f1.a, f1.b, f2.a, f2.b
from f1 cross join f2
если для декартова произведения таблиц наложить условия, тогда получим
внутреннее закрытое соединение таблиц
with
f1( a, b ) as (
select 1 as a, 'type 1' as b from dual
union all
select a + 1, b
from f1
where a < 10
),
f2( a, b ) as (
select 5 as a, 'type 2' as b from dual
union all
select a + 1, b
from f2
where a < 15
)
select f1.a, f1.b, f2.a, f2.b
from f1 cross join f2
where f1.a = f2.a
Стоит отметить, что допускается в синтаксисе внутреннего inner закрытого соединения
from Table1 t1 inner join Table2 t2
on t1.id = t2.id
опускать тип соединения
from Table1 t1 join Table2 t2
on t1.id = t2.id
Также сказанное справедливо и для внешних outer соединений (полного, левого полуоткрытого и правого полуоткрытого).
full outer join -> full join
left outer join -> left join
right outer join -> right join
Отличительная особенность декартова произведения — вероятный большой объем результата, а значит более высокая нагрузка на сервер. Поэтому в соединениях необходимо использовать новый синтаксис, так как этот синтаксис требует указания сравнения значений разных столбцов друг с другом (если, конечно, это не natural inner join). Но если все же по каким-то причинам необходимо получить декартово произведение таблиц, как было упомянуто выше, правильней использовать cross join.