out join 和 inner join

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24496749/viewspace-729964/