ITPub博客

首页 > Linux操作系统 > Linux操作系统 > out join 和 inner join

out join 和 inner join

原创 Linux操作系统 作者:shuangoracle 时间:2012-05-16 22:30:29 0 删除 编辑
create table table1(object_id number,object_name varchar2(30));
insert into table1 (OBJECT_ID, OBJECT_NAME)
values (20, 'ICOL$');
insert into table1 (OBJECT_ID, OBJECT_NAME)
values (44, 'I_USER1');
insert into table1 (OBJECT_ID, OBJECT_NAME)
values (28, 'CON$');
insert into table1 (OBJECT_ID, OBJECT_NAME)
values (15, 'UNDO$');
insert into table1 (OBJECT_ID, OBJECT_NAME)
values (29, 'C_COBJ#');
commit;

create table table2(object_id number,object_name varchar2(30));
insert into table2 (OBJECT_ID, OBJECT_NAME) 
values(20,'ICOL$');
insert into table2 (OBJECT_ID, OBJECT_NAME)
values(44,'I_USER1');
insert into table2 (OBJECT_ID, OBJECT_NAME)
values(44,'I_USER1');
insert into table2 (OBJECT_ID, OBJECT_NAME)
values(null,'C_COBJ#');
commit;

1、
SQL> select t1.object_id   object_id_1,
  2         t1.object_name object_name_1,
  3         t2.object_id   object_id_2,
  4         t2.object_name object_name_2
  5    from table1 t1
  6    left outer join table2 t2
  7      on (t1.object_id = t2.object_id and t2.object_id=44);
 
OBJECT_ID_1 OBJECT_NAME_1                  OBJECT_ID_2 OBJECT_NAME_2
----------- ------------------------------ ----------- ------------------------------
         44 I_USER1                                 44 I_USER1
         44 I_USER1                                 44 I_USER1
         29 C_COBJ#                                    
         28 CON$                                       
         15 UNDO$                                      
         20 ICOL$                                      
 
6 rows selected
2、
SQL> select t1.object_id   object_id_1,
  2         t1.object_name object_name_1,
  3         t2.object_id   object_id_2,
  4         t2.object_name object_name_2
  5    from table1 t1
  6    left outer join table2 t2
  7      on t1.object_id = t2.object_id and t2.object_id=44;
 
OBJECT_ID_1 OBJECT_NAME_1                  OBJECT_ID_2 OBJECT_NAME_2
----------- ------------------------------ ----------- ------------------------------
         44 I_USER1                                 44 I_USER1
         44 I_USER1                                 44 I_USER1
         29 C_COBJ#                                    
         28 CON$                                       
         15 UNDO$                                      
         20 ICOL$                                      
 
6 rows selected
前两个的区别在于on后的条件加括号和不加括号一样,都是关于table2的关联条件。
也就是说后来加的t2.object_id=44是先在table2中过滤,然后再将结果集和table1进行关联。

3、
SQL> select t1.object_id   object_id_1,
  2         t1.object_name object_name_1,
  3         t2.object_id   object_id_2,
  4         t2.object_name object_name_2
  5    from table1 t1
  6    left outer join table2 t2
  7      on t1.object_id = t2.object_id
  8   where t2.object_id = 44;
 
OBJECT_ID_1 OBJECT_NAME_1                  OBJECT_ID_2 OBJECT_NAME_2
----------- ------------------------------ ----------- ------------------------------
         44 I_USER1                                 44 I_USER1
         44 I_USER1                                 44 I_USER1
4、
SQL>  select t1.object_id   object_id_1,
  2          t1.object_name object_name_1,
  3          t2.object_id   object_id_2,
  4          t2.object_name object_name_2
  5     from table1 t1
  6    inner join table2 t2
  7       on t1.object_id = t2.object_id
  8    where t2.object_id = 44;
 
OBJECT_ID_1 OBJECT_NAME_1                  OBJECT_ID_2 OBJECT_NAME_2
----------- ------------------------------ ----------- ------------------------------
         44 I_USER1                                 44 I_USER1
         44 I_USER1                                 44 I_USER1
         
从3和4可以看出,当在where条件中限制table2的条件时,left outer join和inner join一样,都相当于将table1和table2关联后的结果中过滤t2.object_id = 44。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24496749/viewspace-729964/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-08-25

  • 博文量
    84
  • 访问量
    207022