oracle中的连接查询

--创建数据表以及添加测试数据
create table TEST_A(ID NUMBER not null)
create table TEST_B(ID NUMBER not null)
insert into TEST_A values(1);  
insert into TEST_A values(2);  
insert into TEST_A values(3);  
insert into TEST_B values(1);  
insert into TEST_B values(2);  
insert into TEST_B values(4);  
commit; 

 

数据表的连接有: 
1、内连接(自然连接): 只有两个表相匹配的行才能在结果集中出现 

 

select * from test_a a ,test_b b where a.id=b.id

 
2、外连接: 包括 
（1）左外连接(左边的表不加限制，即以左边的表为主表) 

  主流关系型数据库用法：a left join b on ...

select * from test_a a left join test_b b on a.id=b.id;

 

  oracle中还可以用(+)，(+)放在“=”号的右边；

 

select * from test_a a ,test_b b where a.id=b.id(+)

 

   查询结果如下：

 

（2）右外连接(右边的表不加限制，即以右边的表为主表) 

  主流关系型数据库用法：a right join b on ...

select * from test_a a right join test_b b on a.id=b.id;

 

  oracle中还可以用(+)，(+)放在“=”号左边；

select * from test_a a ,test_b b where a.id(+)=b.id;

  

查询结果如下：

 

（3）全外连接(左右两表都不加限制) 

  主流关系型数据库用法：a full join b on ...

select * from test_a a full join test_b b on a.id=b.id;

 

  oracle中还可以用两个带(+)的查询结果的union

select * from test_a a,test_b b where a.id(+)=b.id 
union 
select * from test_a a,test_b b where a.id=b.id(+);

  

查询结果如下：

 

4、交叉连接，也加完全连接或者笛卡尔积

主流关系型数据库用法：a cross join b

 

select * from test_a,test_b;

 或者

 

 

select * from test_a cross join test_b;

 

查询结果如下：

 

 

 5、自然连接

虽然natural join(自然连接)实际上的用的比较少，但实际上这个连接是非常有用的，若能经常使用一下，实际上是非常方便的。

自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果。

 

给上述两张表分别加上name1，name2字段，方便更好的理解自然连接。

ALTER TABLE test_a ADD name1  varchar2(32) ;

ALTER TABLE test_b ADD name2  varchar2(32) ;

 

并且增加相应的值。

 

执行下面的代码：

select a.name1,b.name2 from test_a a natural join test_b b;

 

查询结果如下：

这里我们并没有指定连接的条件，实际上oracle为我们自作主张的将test_a表中的id和test_b表中的id做了连接。也就是实际上相当于：

select a.name1,b.name2 from test_a a join test_b b on a.id=b.id;

 因为这两张表的这两个字段id的类型个名称完全相同，所以使用natural join时被自然的连接在一起了。

 

另外：

1.如果做自然连接的两个表的有多个字段都满足有相同名称个类型，那么他们会被作为自然连接的条件。

2.如果自然连接的两个表仅是字段名称相同，但数据类型不同，那么将会返回一个错误。

3.由于oracle中可以进行这种非常简单的natural join，我们在设计表时，应该尽量在不同表中具有相同含义的字段使用相同的名字和数据类型，以方便以后使用natural join。

 

 

ps：

注意以下两种情况：

1、

select * from test_a a,test_b b where a.id=b.id(+) and b.id=2;

 

查询结果如下：

 

 

2、

select * from test_a a,test_b b where a.id=b.id(+) and b.id(+)=2;

 

查询结果如下：