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

 

--创建一个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;