1，创建测试表
复制代码 代码示例:
-- Create table
create table TABLE_A
(
FIELD_AA VARCHAR2(10),
FIELD_AB VARCHAR2(10)
)
-- Create table
create table TABLE_B
(
FIELD_BA VARCHAR2(10),
FIELD_BB VARCHAR2(10)
)
2，插入测试数据
复制代码 代码示例:
insert into TABLE_A (FIELD_AA, FIELD_AB)
values ('1', 'a');
insert into TABLE_A (FIELD_AA, FIELD_AB)
values ('3', 'b');
insert into TABLE_A (FIELD_AA, FIELD_AB)
values ('4', 'c');
commit;
insert into TABLE_B (FIELD_BA, FIELD_BB)
values ('1', 'x');
insert into TABLE_B (FIELD_BA, FIELD_BB)
values ('2', 'y');
insert into TABLE_B (FIELD_BA, FIELD_BB)
values ('4', 'z');
commit;
3，oracle数据库连接介绍
3.1内连接
复制代码 代码示例:
-- 内连接(查询出两个表相匹配的记录)
select *
from table_a
inner join table_b on table_a.field_aa = table_b.field_ba;
-- 相同写法
select * from table_a, table_b where table_a.field_aa = table_b.field_ba;
3.2左外连接
复制代码 代码示例:
-- 左连接(显示左表<驱动表>中的所有数据,此例中为table_a)
select *
from table_a
left join table_b on table_a.field_aa = table_b.field_ba ;
-- 相同写法
select *
from table_a, table_b
where table_a.field_aa = table_b.field_ba(+) ;
3.3右外连接
复制代码 代码示例:
-- 右连接(显示右表<被驱动表>中的所有数据,此例中为table_b)
select *
from table_a
right join table_b on table_a.field_aa = table_b.field_ba;
-- 相同写法
select *
from table_a, table_b
where table_a.field_aa(+) = table_b.field_ba;
3.4全外连接
复制代码 代码示例:
-- 全外连接(等值连接的基础上将左表和右表的未匹配数据都加上)
select *
from table_a
full join table_b on table_a.field_aa = table_b.field_ba;
-- 全外连接的等价写法，对同一表先做左连接，然后右连接
select *
from table_a
left join table_b on table_a.field_aa = table_b.field_ba
union
select *
from table_a
right join table_b on table_a.field_aa = table_b.field_ba;
3.5带条件的连接写法与差异
复制代码 代码示例:
-- 带条件的左连接
select *
from table_a
left join table_b on table_a.field_aa = table_b.field_ba and table_b.field_bb = 'y'; -- 先过滤再连接(先把table_b表中符合条件的数据过滤出来,之后再跟table_a进行left join操作)
select *
from table_a
left join table_b on table_a.field_aa = table_b.field_ba where table_b.field_bb = 'y'; -- 先连接再过滤(也就是说先按照left jion的on条件关联出一个虚拟表,之后再对这个虚拟表进行where后的过滤操作)
select *
from table_a, table_b
where table_a.field_aa = table_b.field_ba(+) and table_b.field_bb = 'y'; -- 先连接再过滤
