oracle左连接查询博客,Oracle中left join,right join,inner join分析

在开始分析之前，我们先准备一些测试数据。首先，初始化三张表，分别为test_user,test_team,test_type,建表语句如下

create table TEST_USER

(

user_id NUMBER,

user_name VARCHAR2(30 CHAR),

user_team NUMBER,

user_type VARCHAR2(30 CHAR)

);

create table test_team(

team_id number,

team_name varchar2(30char)

);

create table test_type(

type_id number,

type_name varchar2(30char)

);初始化数据

insert into TEST_USER (USER_ID, USER_NAME, USER_TEAM, USER_TYPE)

values (1, 'user1', 1, '1');

insert into TEST_USER (USER_ID, USER_NAME, USER_TEAM, USER_TYPE)

values (2, 'user2', 2, null);

insert into TEST_USER (USER_ID, USER_NAME, USER_TEAM, USER_TYPE)

values (3, 'user3', 5, '2');

insert into TEST_USER (USER_ID, USER_NAME, USER_TEAM, USER_TYPE)

values (4, 'user4', null, '3');

insert into TEST_USER (USER_ID, USER_NAME, USER_TEAM, USER_TYPE)

values (5, 'user5', 3, '6');

insert into TEST_TEAM (TEAM_ID, TEAM_NAME)

values (1, 'team1');

insert into TEST_TEAM (TEAM_ID, TEAM_NAME)

values (2, 'team2');

insert into TEST_TEAM (TEAM_ID, TEAM_NAME)

values (3, 'team3');

insert into TEST_TEAM (TEAM_ID, TEAM_NAME)

values (4, 'team4');

insert into TEST_TYPE (TYPE_ID, TYPE_NAME)

values (1, 'type1');

insert into TEST_TYPE (TYPE_ID, TYPE_NAME)

values (2, 'type2');

insert into TEST_TYPE (TYPE_ID, TYPE_NAME)

values (3, 'type3');三张表的数据，如下表格

表：test_user

USER_ID

USER_NAME

USER_TEAM

USER_TYPE

1

user1

1

1

2

user2

2

3

user3

5

2

4

user4

3

5

user5

3

6 表：test_team

TEAM_ID

TEAM_NAME

1

team1

2

team2

3

team3

4

team4

表：test_type

TYPE_ID

TYPE_NAME

1

type1

2

type2

3

type3

一、左连接  left join

左连接是把left join左边表的数据都查询出来，具体用法，比如

select * from test_user a left join test_team b on a.user_team=b.team_id查询结果如下

这里left join语句中左侧表，即test_user表所有记录都会查询出来，on关键字后面跟的关联条件应理解为匹配条件，对于left join右侧的表，即test_team表满足匹配条件的，显示对应右侧表的记录信息，不满足匹配条件右侧表对应字段的记录显示为NULL。

下面我们再来看一个sql

select * from test_user a left join test_team b on a.user_team=b.team_id and b.team_id=1查询结果如下

通过上述的描述可知，表test_user和test_team满足a.user_team=b.team_id and b.team_id=1匹配条件的只有第一条记录

其他不满足匹配条件的test_team对应字段显示为NULL.

我们继续查询一个sql

select * from test_user a left join test_team b on a.user_team=b.team_id and a.user_id=2查询结果如下

同上，表test_user和test_team满足a.user_team=b.team_id and a.user_id=2匹配条件的只有第一条记录，其他不满足匹配条件的test_team对应字段显示为NULL.

关于on条件与where的区别

我们将sql

select * from test_user a left join test_team b on a.user_team=b.team_id and b.team_id=1中的and该为where

select * from test_user a left join test_team b on a.user_team=b.team_id where b.team_id=1查询结果如下

这里的where应理解过滤条件，查询是会先按on后的匹配条件a.user_team=b.team_id形成符合要求的临时表，然后where后面的条件b.team_id=1剔除不符合条件的记录。

同理，另一个改写的sql

select * from test_user a left join test_team b on a.user_team=b.team_id where a.user_id=2查询结果如下

oracle中的(+)

在oracle中左连接left join可以用左连接的表,在其关联关系一侧加(+)表示，

比如左连接sql

select * from test_user a left join test_team b on a.user_team=b.team_id改写成(+)形式，有如下两种方式，效果是一样的

select * from test_user a,test_team b where a.user_team=b.team_id(+);或者

select * from test_user a,test_team b where b.team_id(+)=a.user_team;oracle中left join和用(+)表达的方式在执行上性能是一样的。对应的执行计划分别如下：

和

这里还有个值得注意的地方，如若sql

select * from test_user a left join test_team b on a.user_team=b.team_id and b.team_id=1

改写成(+)该如何改写呢？

这样吗？

这样改写是错的，从查询结果就可以看出来。因为where后跟的是过滤条件，在

select * from test_user a left join test_team b on a.user_team=b.team_id and b.team_id=1

中b.team_id=1在on后面作为匹配条件，不影响结果返回的条数，不会根据条件对结果进行剔除。这里将b.team_id=1放在where是过滤条件。

正确的改写方式，应按照上面的强调的 左连接left join可以用左连接的表,在其关联关系一侧加(+)表示(其实右连接right join也一样)，即改写如下

select * from test_user a,test_team b where a.user_team=b.team_id(+) and b.team_id(+)=1;查询结果如下

二、右连接 right join

右连接是把right join左边表的数据都查询出来，具体用法，比如

select * from test_type a right join test_user b on a.type_id=b.user_type查询结果如下

这里right join语句中右侧表，即test_user表所有记录都会查询出来，on关键字后面跟的关联条件应理解为匹配条件，对于right join左侧的表，即test_type表满足匹配条件的，显

示对应右侧表的记录信息，不满足匹配条件左侧表对应字段的记录显示为NULL。

对于right join可以类比left join来看，包括在oracle用(+)来进行改写，只不过(+)要加在right join左侧表在关联关系中的一侧。

三、内连接inner join

如果说right join，left join是拿一张表去匹配另一张表，匹配上信息对应信息，否则返回NULL,而内连接呢相当于两张表相互匹配，取其交集。即相当于where后的=连接。

所以下方两个sql是等效的。

和