-- 一个小示例分清 左右连接 与 外连接 关系
-- 表顺序 t1 t2（注意表的前后排列顺序）
-- 左连接，（+）号在右边，也就是 t2 这边，（+）号边交集
-- 右连接，（+）号在左边，也就是 t1 这边，（+）号边交集
select * from(
select 1 a, 2 b from dual
union all
select 2 a, 3 b from dual
union all
select 3 a, 4 b from dual
) t1
-- left join 就是以左边集（t1）为全集，左边的全集 t1 全取出来，而右边集取 t1 t2 交集
left join (
select 1 a, 2 b from dual
union all
select 1 a, 7 b from dual
union all
select 2 a, 7 b from dual
union all
select 4 a, 7 b from dual
) t2
on t1.a = t2.a
select * from(
select 1 a, 2 b from dual
union all
select 2 a, 3 b from dual
union all
select 3 a, 4 b from dual
) t1,
(
select 1 a, 2 b from dual
union all
select 1 a, 7 b from dual
union all
select 2 a, 7 b from dual
union all
select 4 a, 7 b from dual
) t2
where 1=1
and t1.a = t2.a(+) -- 找出 （t1 t2 交集）+ （t1 在 t2 的差集）
select * from(
select 1 a, 2 b from dual
union all
select 2 a, 3 b from dual
union all
select 3 a, 4 b from dual
) t1
-- right join 就是以右边集（t2）为全集，左边集取 t1 t2 交集，而右边全集 t1 全取出来
right join (
select 1 a, 2 b from dual
union all
select 1 a, 7 b from dual
union all
select 2 a, 7 b from dual
union all
select 4 a, 7 b from dual
) t2
on t1.a = t2.a
select * from(
select 1 a, 2 b from dual
union all
select 2 a, 3 b from dual
union all
select 3 a, 4 b from dual
) t1,
(
select 1 a, 2 b from dual
union all
select 1 a, 7 b from dual
union all
select 2 a, 7 b from dual
union all
select 4 a, 7 b from dual
) t2
where 1=1
and t1.a(+) = t2.a -- 找出 （t1 t2 交集）+ （t2 在 t1 的差集）
select * from(
select 1 a, 2 b from dual
union all
select 2 a, 3 b from dual
union all
select 3 a, 4 b from dual
) t1
-- full join = left join + rigth join 就是取 t1 t2 交集，同时也取 t1 t2 各自差集
full join (
select 1 a, 2 b from dual
union all
select 2 a, 7 b from dual
union all
select 4 a, 7 b from dual
) t2
on t1.a = t2.a