《Pro Oracle SQL》Chapter3 -- 3.3.5 Outer Joins

Outer Joins    外连接      (page 120)
     An outer join returns all rows from one table and only those rows from the joined table where the join
condition is met.  Oracle uses the  + character to indicate an outer join.  The  + is placed in parentheses
on the side of the join condition with the table where only rows that match is located.   As I’ve indicated
in each of the join method overviews, outer joins will require that the outer joined table be the driving
table.   This can mean that join orders that might be more optimal will not be used.  So, use outer joins
properly with care since their use has implications related to performance of the overall plan.
    外连接返回一表中的所有行以及那些来至连接表仅满足连接条件的行。Oracle 使用加号(+)表示一外连接。置于括号内的加号放在连接条件上，位于(提供)匹配行的表的一方。 正如我在每种连接方法的概述中所指出的，外连接需要外连接表作为驱动表 。这意味着未使用过的连接顺序可能具有更佳的性能（改变连接顺序将会带来更优的性能）。因此，适当的关注外连接的使用，因为他们的使用与全局计划的性能有隐含的关联。
    Listing 3-23 shows an example of how outer joins work.  In the example, you have been asked to
produce a count of how many customers have placed between $0 and $5000 in orders.
    列表3-23展示了一个外连接如何工作的例子。在例子中，你要求计算多少顾客的订单额在$0 到 $5000 之间
Listing 3-23. Outer Join  
SQL> -- Query to show customers with total orders between $0 and $5000     查询展示总订单额在$0到$5000之间的顾客
SQL> select c.cust_last_name, nvl(sum(o.order_total),0) tot_orders
  2    from customers c, orders o
  3   where c.customer_id = o.customer_id
  4   group by c.cust_last_name
  5  having nvl(sum(o.order_total),0) between 0 and 5000
  6   order by c.cust_last_name ;
 
CUST_LAST_NAME            TOT_ORDERS
--------------------                 ---------------
Alexander                            309
Chandar                              510
George                               220
Higgins                               416
Kazan                                1233
Sen                                    4797
Stern                                 969.2
Weaver                              600
 
8 rows selected.

SQL> -- To produce just a count, modify the query slightly
SQL> select count(*) ct
  2    from
  3  (
  4  select c.cust_last_name, nvl(sum(o.order_total),0) tot_orders
  5    from customers c, orders o
  6   where c.customer_id = o.customer_id
  7   group by c.cust_last_name
  8  having nvl(sum(o.order_total),0) between 0 and 5000
  9   order by c.cust_last_name
 10  );

             CT
---------------
              8

1 row selected.
SQL> -- What about customers who haven’t placed orders (they would have $0 order amount)?

        --那些没有下订单的顾客(他们将是$0的订单量)会怎样？
SQL> -- Change the query to an outer join to include customers without orders

        -- 把查询改成外连接，包括没有订单的顾客
SQL> select count(*) ct
  2    from
  3  (
  4  select c.cust_last_name, nvl(sum(o.order_total),0) tot_orders
  5    from customers c, orders o
  6   where c.customer_id = o.customer_id(+) (注：加号在右边，等号左边的表的行全返回。就是LEFT OUTER  JOIN 反之亦然)
  7   group by c.cust_last_name
  8  having nvl(sum(o.order_total),0) between 0 and 5000
  9   order by c.cust_last_name
 10  );
 
             CT
---------------
            140
1 row selected.

 SQL> set autotrace traceonly explain
SQL> /
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3042670853
------------------------------------------------------------------------------------------
| Id  | Operation                                                        | Name                          | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                      |                                    |     1   |          |     5  (20)|
|   1 |   SORT AGGREGATE                                      |                                    |     1   |          |               |
|   2 |      VIEW                                                         |                                    |     1   |          |     5  (20)|
|*  3 |        FILTER                                                    |                                    |          |          |               |
|   4 |           HASH GROUP BY                                  |                                   |     1   |    22   |     5  (20)|
|   5 |              NESTED LOOPS OUTER                     |                                    |   319  |  7018 |     4   (0)|
|   6 |                 VIEW                                              | index$_join$_002          |   319 |  3828 |     3   (0)|
|*  7 |                   HASH JOIN                                   |                                    |          |          |              |
|   8 |                      INDEX FAST FULL SCAN            | CUSTOMERS_PK        |   319 |  3828 |     1   (0)|
|   9 |                      INDEX FAST FULL SCAN            | CUST_LNAME_IX        |   319 |  3828 |     1   (0)|
|  10 |                TABLE ACCESS BY INDEX ROWID | ORDERS                     |     1   |    10   |     1   (0)|
|* 11 |                  INDEX RANGE SCAN                    | ORD_CUSTOMER_IX  |     2   |           |     0   (0)|
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------------------------------------------------
   3 - filter(NVL(SUM("O"."ORDER_TOTAL"),0)>=0 AND (NVL(SUM("O"."ORDER_TOTAL"),0)<=5000)
   7- access(ROWID=ROWID)
  11 - access("C"."CUSTOMER_ID"="O"."CUSTOMER_ID"(+))
         filter("O"."CUSTOMER_ID"(+)>0)

    The example shows how the original answer wasn't exactly correct without using an outer join.Since customers who haven't yet placed orders would not have rows in the order table, they would not be included in the query result set.Changing the query to be an outer join will cause those customers to be included. Also notice the plan operation on line 5 that specifies the NESTED LOOPS OUTER. Outer joins can be used with any join method(nested loops,hash,sort-merge) and will be denoted with the word OUTER at the end of the normal operation name.
    例子展示了不使用外连接的话，为什么最初的答案不准确。因为还没下订单的顾客在order表中将没有行(记录)，他们将不包含在查询结果集中。把查询改成外连接将会包括这些客户。也要注意在计划操作的第5行指出NESTED LOOPS OUTER。外连接可以连同任何连接方法(嵌套循环，哈希，排序-合并)一起，只是在正常操作名称的后面用单词OUTER标示。
    As mentioned earlier, the use of the (+) operator to denote an outer join is Oracle-specific syntax. The same thing can be accomplished using ANSI join syntax as well, as shown in Listing 3-24.
    如前提及的，使用(+)操作符标示外连接是Oracle专属语法。同样的事情也能使用ANSI的连接语法，如列表3-24所示。
Listing 3-24. Outer Join Using ANSI Join Syntax
SQL> select count(*) ct
    2      from
    3   (
    4   select c.cust_last_name, nvl(sum(o.order_total),0) tot_orders
    5      from customers c
    6              left outer join
    7            orders o
    8             on( c.customer_id = o.customer_id )
    9      group by c.cust_last_name
   10    having nvl(sum(o.order_total),0) betwen 0 and 5000
   11      order by c.cust_last_name
   12    );
                     CT
 --------------------------
                  140
1 row selected.

    With ANSI syntax, you simply use the keywords LEFT OUTER JOIN. This indicates that the table on the left( i.e. the first table listed ) is the one that you want to have all rows included even if a match on the join condition isn't found. You could use RIGHT OUTER JOIN if you wanted to have all rows from orders included even if there was no match in customers.
    使用ANSI句法，你只要使用关键字LEFT OUTER JOIN。这表示左边的表(例如所列第一个表)是你想要(它的)所有行被包括的(那张表)，即使连接条件上一次匹配都没找到。你也可以使用RIGHT OUTER JOIN，如果你想要包含来自orders表的所有行，即使在customers中没有匹配。
     When you use the Oracle(+) operator, you have some limitations that do not exist if you use ANSI syntax. Oracle will throw an error if you attempt to outer join the same table to more than one other table. The error message you get is "ORA-01417: a table may be outer joined to at most one other table". With ANSI syntax, there is no limit on the number of tables to which a single table can be outer-joined.
    当你用Oracle(+)操作符，你将受到一些限制，而如果你用ANSI句法则没有。如果你企图外连接同一张表到多个其它表上，Oracle将报错。你得到的错误信息是"ORA-01417: a table may be outer joined to at most one other table"。使用ANSI句法，不会限制单个表能外连接的(到其它)表的数量。
    Another limitation of Oracle’s outer join syntax is that it doesn’t support full outer joins.  A full
outer join will join two tables from left-to-right and right-to-left.  Records that join in both directions
are output once to avoid duplication.  To demonstrate a full outer join, Listing 3-25 shows the creation
of two tables that contain a small subset of common data but have some data that is only present in the
single table.  The full outer join will return all the rows from both tables that match plus the rows that
are unique to each table.
    另一个限制是Oracle的外连接句法不支持全外连接。全外连接将连接两表"从左到右"再"从右到左"。两个方向连接的记录只输出一次避免重复。 为了演示 全外连接，列表3-25展示了两表的创建，它们包含一小子集的共同数据，但是又有些数据只存于单表中。全外连接将返回取自两表的所有行集，匹配的再加上在 各表中都唯一的。
Listing 3-25. Full Outer Join Using ANSI Join Syntax
SQL> create table e1 as select * from emp where deptno in (10,20);
 
Table created.
 
SQL> create table e2 as select * from emp where deptno in (20,30);
 
Table created.
 
SQL> select e1.ename, e1.deptno, e1.job
  2        ,e2.ename, e2.deptno, e2.job
  3        from   e1
  4         full outer join
  5         e2
  6         on (e1.empno = e2.empno);
ENAME               DEPTNO               JOB              ENAME               DEPTNO     JOB
----------               ---------------          ---------           ----------            ---------------  ---------
SMITH                   20                     CLERK           SMITH                   20           CLERK
JONES                   20                    MANAGER      JONES                  20           MANAGER
SCOTT                   20                    ANALYST        SCOTT                 20            ANALYST
ADAMS                  20                    CLERK            ADAMS                 20            CLERK
FORD                    20                    ANALYST         FORD                   20            ANALYST
CLARK                   10                   MANAGER
MILLER                  10                   CLERK
                                                                            TURNER               30             SALESMAN
                                                                            BLAKE                  30             MANAGER
                                                                            ALLEN                  30             SALESMAN
                                                                            WARD                  30             SALESMAN
                                                                            MARTIN                30            SALESMAN
                                                                            JAMES                 30             CLERK

13 rows selected.
 
SQL> set autotrace traceonly explain
SQL> /
 Execution Plan
----------------------------------------------------------
Plan hash value: 3117905978
 
-----------------------------------------------------------------------------
| Id  | Operation                             | Name | Rows| Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |    11  |   572 |    13   (8)       | 00:00:01 |
|   1 |   VIEW                                 |          |    11  |   572 |    13   (8)        | 00:00:01 |
|   2 |      UNION-ALL                      |          |         |          |                      |          |
|*  3 |        HASH JOIN OUTER        |          |     7   |   448 |     7  (15)        | 00:00:01 |
|   4 |           TABLE ACCESS FULL | E1     |     7  |   245  |     3   (0)        | 00:00:01 |
|   5 |           TABLE ACCESS FULL | E2     |    11  |   319 |     3   (0)         | 00:00:01 |
|*  6 |        HASH JOIN ANTI            |          |     4   |   100 |     7  (15)        | 00:00:01 |
|   7 |           TABLE ACCESS FULL | E2     |    11  |   231 |     3   (0)         | 00:00:01 |
|   8 |           TABLE ACCESS FULL | E1     |     7   |    28  |     3   (0)        | 00:00:01 |
-----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("E1"."EMPNO"="E2"."EMPNO"(+))
   6 - access("E1"."EMPNO"="E2"."EMPNO")

    Note that rows from both tables appear in the output, even if they do not have a match in the
opposite table. This is what a full outer join does and can be useful when partial datasets need to be
joined.  As you can see from the plan, the full outer join actually executed two separate query blocks
(one HASH JOIN OUTER  and one  HASH JOIN ANTI) and appended the results with  UNION ALL .
    注意来自两表的行都出现在了结果集中，即使他们不匹配对应的表。 这就是全外连接所做的，当部分数据集需要连接时是有用的。从计划中你可以看出，全外连接实际执行两个独立的查询块(一个HASH JOIN OUTER和一个HASH JOIN ANTI)在用UNION ALL合并结果。
    Using the plan from the ANSI full outer join example, you could write an equivalent statement
using Oracle’s syntax that would result in the same final result set.  Listing 3-26 shows how the
statement would be coded.
    根据ANSI全外连接例子的计划，你可用Oracle句法写出生成最终一样的结果集的等价语句。列表3-26展示了语句如何编写。
Listing 3-26. Oracle Equivalent Syntax for Full Outer Join Functionality     Oracle等价句法实现全外连接功能
SQL> select e1.ename, e1.deptno, e1.job,
  2         e2.ename, e2.deptno, e2.job
  3  from   e1,
  4         e2
  5  where  e1.empno (+) = e2.empno
  6  union
  7  select e1.ename, e1.deptno, e1.job,
  8         e2.ename, e2.deptno, e2.job
  9  from   e1,
 10         e2
 11  where  e1.empno = e2.empno (+);

ENAME               DEPTNO               JOB              ENAME               DEPTNO     JOB
----------               ---------------          ---------           ----------            ---------------  ---------
ADAMS                  20                    CLERK            ADAMS                 20            CLERK
CLARK                   10                   MANAGER
JONES                   20                    MANAGER      JONES                  20           MANAGER
FORD                    20                    ANALYST         FORD                   20            ANALYST
MILLER                  10                   CLERK
SCOTT                   20                    ANALYST        SCOTT                 20            ANALYST
SMITH                   20                     CLERK           SMITH                   20           CLERK                            
                                                                            ALLEN                  30             SALESMAN
                                                                            BLAKE                  30             MANAGER
                                                                            JAMES                 30             CLERK
                                                                            MARTIN                30            SALESMAN
                                                                            TURNER               30             SALESMAN
                                                                            WARD                  30             SALESMAN
13 rows selected.
 
SQL> set autotrace traceonly explain
SQL> /
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3941775845
 
-----------------------------------------------------------------------------
| Id  | Operation                              | Name | Rows  | Bytes | Cost (%CPU)  | Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |    18   |   756   |    15  (60)      | 00:00:01 |
|   1 |   SORT UNIQUE                   |           |    18   |   756   |    15  (60)       | 00:00:01 |
|   2 |      UNION-ALL                      |           |          |            |                       |          |
|*  3 |        HASH JOIN OUTER       |          |    11   |   462    |     7  (15)        | 00:00:01 |
|   4 |           TABLE ACCESS FULL| E2      |    11   |   231    |     3   (0)         | 00:00:01 |
|   5 |           TABLE ACCESS FULL| E1     |     7     |   147   |     3   (0)         | 00:00:01 |
|*  6 |        HASH JOIN OUTER       |          |     7    |   294   |     7  (15)         | 00:00:01 |
|   7 |           TABLE ACCESS FULL | E1     |     7   |   147    |     3   (0)          | 00:00:01 |
|   8 |           TABLE ACCESS FULL | E2     |    11   |   231   |     3   (0)          | 00:00:01 |
-----------------------------------------------------------------------------
 Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("E1"."EMPNO"(+)="E2"."EMPNO")
   6 - access("E1"."EMPNO"="E2"."EMPNO"(+))
 
    You may have noticed that the Oracle equivalent plan is just a bit different from the ANSI plan. 
Oracle uses two outer joins, one in each direction, which is exactly what you asked it to do.  So you could use Oracle syntax to accomplish a full outer join, but the ANSI syntax is certainly more straightforward.  Also keep in mind that full outer joins can be quite costly in terms of the amount of resources required to execute.  Always be careful to understand the implications of coding such queries and note the performance implications.
    你可能已经主要到Oracle等价计划和ANSI计划有点不同。Oracle用了两个外连接，每个方向一个，是你要求它准确去做的。因此你也能用 Oracle句法完成全外连接，但是ANSI句法当然更加直接。记住全外连接代价非常高，根据执行时请求的资源量而言。请总是小心的理解这种查询的代码的含义以及注意性能的含义。
Summary    总结
    The optimizer must make a few key choices when determining the execution plan for any SQL
statement.  First, the best way to access each table used in the statement has to be determined.  There
are basically two choices: an index or a full table scan.  Each access method works differently to access
the blocks containing the row data your SQL statement needs.  Once the optimizer chooses the access
methods, the join methods have to be selected.  Tables will be joined together in pairs with the row
source from one join result being used to join to another table until all the tables are joined to produce
the final result set.  
    当确定任意SQL语句的执行计划时，优化器必须做出几项关键的选择。首先，在语句中所用的每张表的最佳访问方式必须确定。有两个基本的选择：索引或者全表扫描。对访问包含你SQL语句所需行的数据块，每种访问方法的工作方式是不同的。一旦优化器选择了访问方法，就要确定连接方法。行源成对的连接，把一次连接的结果再连接到另一张表，直到所有的表都连在一起，生成最终的结果集。
    Understanding how each access and join method works can help you write your SQL so that the
optimizer can make the most efficient choices.  Being able to review the execution plans, understand
the operations chosen, and how those operations work will also help you notice areas where
performance problems might occur.  Once again, knowing what is under the hood will help you write
better, faster SQL.
    理解每种访问和连接方法是如何工作的，有助于你写出能让优化器做出更有效率选择的SQL。 可查看执行计划，理解操作选择，和这些操作是如何工作的，也将帮助你关注性能问题可能出现的地方。 重复一遍，知道面罩下面是什么将有助于你写出更好，更快的SQL。