join,left join and where的测试

SQL> select * from t2;


        ID NAME
---------- -------------
         2 c
         3 d
           e
         2 f


SQL> select * from t1;


        ID NAME
---------- --------------------
         1 a

         2 b

SQL> select * from t1 left join t2 on t1.id=t2.id;


        ID NAME                         ID NAME
---------- -------------------- ---------- ------------
         2 b                             2 c
         2 b                             2 f
         1 a

join后的and是先过滤表t2，然后再去和t1连接

SQL> select * from t1 left join t2 on t1.id=t2.id and t2.name<>'c';


        ID NAME                         ID NAME
---------- -------------------- ---------- --------------------
         2 b                             2 f
         1 a

执行计划中也是能看出来的额

--------------------------------------------------------------------------------
Plan hash value: 1823443478


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    80 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     2 |    80 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     2 |    40 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     3 |    60 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------


   1 - access("T1"."ID"="T2"."ID"(+))
   3 - filter("T2"."NAME"(+)<>'c')

在where条件后面添加t2的过滤条件，后，先过滤t2,然后t1与t2的关联就变成了等值连接，不是外连接了

SQL> explain plan for select * from t1 left join t2 on t1.id=t2.id where t2.name<>'c';


已解释。


SQL> select * from table(dbms_xplan.display());


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1838229974


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    80 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     2 |    80 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     2 |    40 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     3 |    60 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------


   1 - access("T1"."ID"="T2"."ID")
   3 - filter("T2"."NAME"<>'c')


select * from t1 left join t2 on t1.id=t2.id where t1.name <>'a';


  ID NAME                         ID NAME
---- -------------------- ---------- --------------------
   2 b                             2 c
   2 b                             2 f

SQL> explain plan for  select * from t1 left join t2 on t1.id=t2.id where t1.name <>'a'


已解释。


SQL> select * from table(dbms_xplan.display());


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1823443478


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    80 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     2 |    80 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |    20 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     4 |    80 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------


   1 - access("T1"."ID"="T2"."ID"(+))
   2 - filter("T1"."NAME"<>'a')

在where后面添加t1的过滤条件后，先过滤t1然后再外连接

在on后面添加t1的过滤条件

SQL> explain plan for  select * from t1 left join t2 on t1.id=t2.id and t1.name <>'a';


已解释。


SQL> select * from table(dbms_xplan.display());


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1823443478


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    80 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     2 |    80 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     2 |    40 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     4 |    80 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------


   1 - access("T1"."ID"="T2"."ID"(+))
       filter("T1"."NAME"<>CASE  WHEN ("T2"."ID"(+) IS NOT NULL) THEN
              'a' ELSE 'a' END )


Note
-----
   - dynamic sampling used for this statement (level=2)


已选择21行。