内外链接，显隐式链接详解

* 博客文章部分截图及内容来自于学习的书本及相应培训课程以及网络其他博客，仅做学习讨论之用，不做商业用途。
* 如有侵权，马上联系我，我立马删除对应链接。
* @author Alan
* @Email no008@foxmail.com

--创建一个user信息表
create table user_info(
id number(2) primary key,
name varchar2(10) unique
);
--插入数据
insert into user_info(id,name)values(1,'曹操');
insert into user_info(id,name)values(2,'刘备');
insert into user_info(id,name)values(3,'周瑜');
insert into user_info(id,name)values(4,'关羽');
insert into user_info(id,name)values(5,'张飞');
insert into user_info(id,name)values(6,'赵云');

select * from user_info;
--创建地址表
create table address(
id number(2) primary key,
user_id number(2),
info varchar2(16)
);
--添加数据
insert into address(id,user_id,info)values(1,1,'河北唐山');
insert into address(id,user_id,info)values(2,2,'北京海淀');
insert into address(id,user_id,info)values(3,1,'河北邯郸');
insert into address(id,user_id,info)values(4,2,'河北保定');
insert into address(id,user_id,info)values(5,3,'河南郑州');
insert into address(id,user_id,info)values(6,5,'山西太原');
insert into address(id,user_id,info)values(7,4,'安徽合肥');
insert into address(id,user_id,info)values(8,2,'山东青岛');
insert into address(id,user_id,info)values(9,9,'山东青岛');
select * from address;

commit;

--隐式左/右外链接，outer可以不写
select u.*,a.* from user_info u left join address a on u.id=a.user_id;
select u.*,a.* from user_info u right join address a on u.id=a.user_id order by a.id;
--显式左/右外链接
select u.*,a.* from user_info u left outer join address a on u.id = a.user_id;
select u.*,a.* from user_info u right outer join address a on u.id = a.user_id;


--隐式内连接，inner可以省略，将on改为where
select u.*,a.* from user_info u,address a where u.id=a.user_id;
--显式内连接,不分左右，不带left或right，只显示重合的数据，也就是取交集
select u.*,a.* from user_info u inner join address a on u.id=a.user_id;


--外链接的其他写法，+号和完整表位置相反
select u.*,a.* from user_info u,address a where u.id=a.user_id(+);--左外链接，+号在=号右面
select u.*,a.* from user_info u,address a where u.id(+)=a.user_id;--右外链接，+号在=号左面

--全连接,显示两个表中所有信息
select u.*,a.* from user_info u full join address a on u.id = a.user_id;
--交叉连接，取笛卡尔积
select u.*,a.* from user_info u cross join address a;
select u.*,a.* from user_info u, address a;