sql - SQL 连接如何工作？

Question

我试图了解联接在内部是如何工作的。以下两个查询的运行方式有何不同？

For example

(A)

Select * 
FROM TABLE1
FULL JOIN TABLE2 ON TABLE1.ID = TABLE2.ID
FULL JOIN TABLE3 ON TABLE1.ID = TABLE3.ID

And

(B)

Select * 
FROM TABLE1
FULL JOIN TABLE2 ON TABLE1.ID = TABLE2.ID
FULL JOIN TABLE3 ON TABLE2.ID = TABLE3.ID

编辑:我在这里谈论的是甲骨文。考虑表 2 和表 3 中存在但表 1 中不存在的一些记录，查询 A 将为该记录提供两行，但 B 将仅提供一行。

Answer 1

您的 DBMS 的优化器将决定如何最好地执行查询。通常这是通过“基于成本的优化”完成的，其中考虑了许多不同的查询计划并选择了最有效的一个。 如果您的两个查询在逻辑上是相同的，无论您以何种方式编写，优化器很可能最终会使用相同的查询计划。事实上，如今基于 SQL 中的这些微小差异生成不同查询计划的优化器将是一个糟糕的优化器。

但是，完全外部联接是另一回事(至少在 Oracle 中是这样)，因为列的联接方式会影响结果。即 2 个查询不可可互换。

您可以在 SQL Plus 中使用 AUTOTRACE 来查看不同的计划:

SQL> select *
  2  from t1
  3  full join t2 on t2.id = t1.id
  4  full join t3 on t3.id = t2.id;

        ID         ID         ID
---------- ---------- ----------
                    1          1

1 row selected.


Execution Plan
----------------------------------------------------------

---------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     3 |   117 |    29  (11)|
|   1 |  VIEW                   |      |     3 |   117 |    29  (11)|
|   2 |   UNION-ALL             |      |       |       |            |
|*  3 |    HASH JOIN OUTER      |      |     2 |   142 |    15  (14)|
|   4 |     VIEW                |      |     2 |    90 |    11  (10)|
|   5 |      UNION-ALL          |      |       |       |            |
|*  6 |       HASH JOIN OUTER   |      |     1 |    91 |     6  (17)|
|   7 |        TABLE ACCESS FULL| T1   |     1 |    52 |     2   (0)|
|   8 |        TABLE ACCESS FULL| T2   |     1 |    39 |     3   (0)|
|*  9 |       HASH JOIN ANTI    |      |     1 |    26 |     6  (17)|
|  10 |        TABLE ACCESS FULL| T2   |     1 |    13 |     3   (0)|
|  11 |        TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)|
|  12 |     TABLE ACCESS FULL   | T3   |     1 |    26 |     3   (0)|
|* 13 |    HASH JOIN ANTI       |      |     1 |    26 |    15  (14)|
|  14 |     TABLE ACCESS FULL   | T3   |     1 |    13 |     3   (0)|
|  15 |     VIEW                |      |     2 |    26 |    11  (10)|
|  16 |      UNION-ALL          |      |       |       |            |
|* 17 |       HASH JOIN OUTER   |      |     1 |    39 |     6  (17)|
|  18 |        TABLE ACCESS FULL| T1   |     1 |    26 |     2   (0)|
|  19 |        TABLE ACCESS FULL| T2   |     1 |    13 |     3   (0)|
|* 20 |       HASH JOIN ANTI    |      |     1 |    26 |     6  (17)|
|  21 |        TABLE ACCESS FULL| T2   |     1 |    13 |     3   (0)|
|  22 |        TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)|
---------------------------------------------------------------------

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

   3 - access("T3"."ID"(+)="T2"."ID")
   6 - access("T2"."ID"(+)="T1"."ID")
   9 - access("T2"."ID"="T1"."ID")
  13 - access("T3"."ID"="T2"."ID")
  17 - access("T2"."ID"(+)="T1"."ID")
  20 - access("T2"."ID"="T1"."ID")

SQL> select *
  2  from t1
  3  full join t2 on t2.id = t1.id
  4  full join t3 on t3.id = t1.id;

        ID         ID         ID
---------- ---------- ----------
                    1
                               1

2 rows selected.


Execution Plan
----------------------------------------------------------

---------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     3 |   117 |    29  (11)|
|   1 |  VIEW                   |      |     3 |   117 |    29  (11)|
|   2 |   UNION-ALL             |      |       |       |            |
|*  3 |    HASH JOIN OUTER      |      |     2 |   142 |    15  (14)|
|   4 |     VIEW                |      |     2 |    90 |    11  (10)|
|   5 |      UNION-ALL          |      |       |       |            |
|*  6 |       HASH JOIN OUTER   |      |     1 |    91 |     6  (17)|
|   7 |        TABLE ACCESS FULL| T1   |     1 |    52 |     2   (0)|
|   8 |        TABLE ACCESS FULL| T2   |     1 |    39 |     3   (0)|
|*  9 |       HASH JOIN ANTI    |      |     1 |    26 |     6  (17)|
|  10 |        TABLE ACCESS FULL| T2   |     1 |    13 |     3   (0)|
|  11 |        TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)|
|  12 |     TABLE ACCESS FULL   | T3   |     1 |    26 |     3   (0)|
|* 13 |    HASH JOIN ANTI       |      |     1 |    26 |    15  (14)|
|  14 |     TABLE ACCESS FULL   | T3   |     1 |    13 |     3   (0)|
|  15 |     VIEW                |      |     2 |    26 |    11  (10)|
|  16 |      UNION-ALL          |      |       |       |            |
|* 17 |       HASH JOIN OUTER   |      |     1 |    39 |     6  (17)|
|  18 |        TABLE ACCESS FULL| T1   |     1 |    26 |     2   (0)|
|  19 |        TABLE ACCESS FULL| T2   |     1 |    13 |     3   (0)|
|* 20 |       HASH JOIN ANTI    |      |     1 |    26 |     6  (17)|
|  21 |        TABLE ACCESS FULL| T2   |     1 |    13 |     3   (0)|
|  22 |        TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)|
---------------------------------------------------------------------

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

   3 - access("T3"."ID"(+)="T1"."ID")
   6 - access("T2"."ID"(+)="T1"."ID")
   9 - access("T2"."ID"="T1"."ID")
  13 - access("T3"."ID"="T1"."ID")
  17 - access("T2"."ID"(+)="T1"."ID")
  20 - access("T2"."ID"="T1"."ID")

实际上，除了Predicate 信息之外，查询计划完全相同