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