oracle SQL 连接如何工作？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/439152/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How does SQL join work?
提问by MOZILLA
I am trying to understand how does joins work internally. What will be the difference between the way in which the following two queries would run?
我试图了解联接在内部是如何工作的。以下两个查询的运行方式有何不同？
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
Edit: I am talking about oracle here. Consider some records present in table 2 and table 3 but not in table 1, query A would give two rows for that record but B would give only one row.
编辑：我在这里谈论oracle。考虑一些存在于表 2 和表 3 但不在表 1 中的记录，查询 A 将为该记录提供两行，但 B 将只提供一行。
回答by Tony Andrews
Your DBMS's optimiser will determine how best to perform the query. Usually this is done by "cost based optimisation", where a number of different query plans are considered and the most efficient one selected. Ifyour two queries are logically identical, it is most likely that the optimiser will end up using the same query plan whichever way you write it. In fact, it would be a poor optimiser these days that produced different query plans based on such minor differences in the SQL.
DBMS 的优化器将决定如何最好地执行查询。通常这是通过“基于成本的优化”完成的，其中考虑了许多不同的查询计划并选择最有效的一个。 如果您的两个查询在逻辑上是相同的，那么无论您以哪种方式编写，优化器很可能最终会使用相同的查询计划。事实上，如今基于 SQL 中的这些细微差异生成不同的查询计划将是一个糟糕的优化器。
However, full outer joins are a different matter (in Oracle at least), since the way the columns are joined influences the result. i.e. the 2 queries are notinterchangeable.
但是，完全外部联接是另一回事（至少在 Oracle 中），因为列的联接方式会影响结果。即 2 个查询不可互换。
You can use AUTOTRACE in SQL Plus to see the different plans:
您可以在 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")
In fact, the query plans are identical except for the Predicate information
实际上，除了 Predicate 信息之外，查询计划是相同的
回答by Amy B
You stated interest in "internals", and then asked an example that illustrates "semantics". I'm answering semantics.
您表示对“内部结构”感兴趣，然后提出了一个说明“语义”的示例。我在回答语义。
Consider these tables.
考虑这些表。
Table1 : 1, 4, 6
Table2 : 2, 4, 5
Table3 : 3, 5, 6
Both examples perform the same join first, so I'll perform that here.
这两个示例首先执行相同的连接，因此我将在此处执行。
FirstResult = T1 FULL JOIN T2 : (T1, T2)
(1, null)
(4, 4)
(6, null)
(null, 2)
(null, 5)
Example (A)
例子（一）
FirstResult FULL JOIN T3 ON FirstItem : (T1, T2, T3)
(1, null, null)
(4, 4, null)
(6, null, 6) <----
(null, 2, null)
(null, 5, null) <----
(null, null, 3)
Example (B)
例子 (B)
FirstResult FULL JOIN T3 ON SecondItem : (T1, T2, T3)
(1, null, null)
(4, 4, null)
(6, null, null) <----
(null, 2, null)
(null, 5, 5) <----
(null, null, 3)
This shows you logically how to produce the results from the joins.
这从逻辑上向您展示了如何从连接中产生结果。
For "internals", there's something called a query optimizer, which will produce these same results - but it will make implementation choices to do the computation/io fast. These choices include:
对于“内部”，有一种叫做查询优化器的东西，它会产生这些相同的结果——但它会做出实现选择来快速进行计算/io。这些选择包括：
- which tables to access first
- look into a table using an index or table scan
- which join implementation type to use (nested loop, merge, hash).
- 首先访问哪些表
- 使用索引或表扫描查看表
- 要使用的连接实现类型（嵌套循环、合并、散列）。
Also note: due to the optimizer making these choices, and changing these choices based on what it considers to be optimal - the order of the results can change. The default ordering of results is always "what is easiest". If you don't want the default ordering, you need to specify ordering in your query.
另请注意：由于优化器做出这些选择，并根据它认为最佳的内容更改这些选择 - 结果的顺序可能会发生变化。结果的默认排序始终是“最简单的”。如果您不想要默认排序，则需要在查询中指定排序。
To see exactly what the optimizer will do with a query (at that moment, because it can change its mind), you need to view the execution plan.
要准确查看优化器将如何处理查询（此时，因为它可以改变主意），您需要查看执行计划。
回答by mbeckish
回答by Gary Myers
With query A what you get includes entries in table 1 with a corresponding entry in table3 without corresponding entries in table3 (nulls for t2 columns)
使用查询 A，您得到的内容包括表 1 中的条目以及表 3 中的相应条目，而表 3 中没有相应条目（t2 列的空值）
With query B uou don't get those entries because you only go to table3 through table2. If you don't have a corresponding entry in table2, the table2.id will be null and will never match a table3.id
使用查询 B uou 不要获取这些条目，因为您只能通过 table2 转到 table3。如果 table2 中没有相应的条目，则 table2.id 将为 null 并且永远不会匹配 table3.id
回答by Shantanu Gupta
For understanding Left/Right join, how they works Check this
为了理解左/右连接，它们是如何工作的检查这个
