<pre name="code" class="html">--创建表a1
create table a1(
aid number,
anum char(20)
)
--为表a1设置主键
alter table a1 add constraint pk_a1 primary key(aid);
--创建与a1表的表结构一致的b表
create table b1 as select * from a where 1=2;
--修改b1表的列名
alter table b1 rename column aid to bid;
alter table b1 rename column anum to bnum;
--为b1表设置主键
alter table b1 add constraint pk_b1 primary key(bid);
insert into a1 values (1,'a01')
(2,'a02')(3,'a03')(5,'a05');
commit;
insert into b1 (bid,bnum) values (1,'b01')
(2,'b02'),
(3,'b03'),
(4,'b04');
commit;
1.左联接查询
select * from a1 left join b1 on a1.aid = b1.bid
结果如下:
结果分析:left join ... on ..是以A表的记录为基础的,也就是说,左表A1的记录将会全部显示出来,
--而右表B1只会显示符合搜索条件的记录。(即a1.aid = b1.bid )
2.右联接查询
select * from a1 right join b1 on a1.aid = b1.bid;
执行结果如下:
3.相等查询或者内连接
select * from a1 inner join b1 on a1.aid = b1.bid;
结果如下:
4.左外联接查询
select * from a1 left outer join b1 on a1.aid = b1.bid;
5.右外联接查询
select * from a1 right outer join b1 on a1.aid = b1.bid;