SQL 连接格式 - 嵌套内部连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5099420/
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
SQL join format - nested inner joins
提问by Tim Peel
I have the following SQL statement in a legacy system I'm refactoring. It is an abbreviated view for the purposes of this question, just returning count(*) for the time being.
我在重构的遗留系统中有以下 SQL 语句。就这个问题而言，这是一个缩写视图，暂时只返回 count(*) 。
SELECT COUNT(*)
FROM Table1
INNER JOIN Table2
INNER JOIN Table3 ON Table2.Key = Table3.Key AND Table2.Key2 = Table3.Key2
ON Table1.DifferentKey = Table3.DifferentKey
It is generating a very large number of records and killing the system, but could someone please explain the syntax? And can this be expressed in any other way?
它正在生成大量记录并杀死系统，但有人可以解释一下语法吗？这可以用任何其他方式表达吗？
- Table1 contains 419 rows
- Table2 contains 3374 rows
- Table3 contains 28182 rows
- 表 1 包含 419 行
- 表 2 包含 3374 行
- 表 3 包含 28182 行
EDIT:
编辑：
Suggested reformat
建议重新格式化
SELECT COUNT(*)
FROM Table1
INNER JOIN Table3
ON Table1.DifferentKey = Table3.DifferentKey
INNER JOIN Table2
ON Table2.Key = Table3.Key AND Table2.Key2 = Table3.Key2
采纳答案by DRapp
For readability, I restructured the query... starting with the apparent top-most level being Table1, which then ties to Table3, and then table3 ties to table2. Much easier to follow if you follow the chain of relationships.
为了可读性，我重新构造了查询...从最顶层开始，表 1 与表 3 相关联，然后表 3 与表 2 相关联。如果您遵循关系链，则更容易遵循。
Now, to answer your question. You are getting a large count as the result of a Cartesian product. For each record in Table1 that matches in Table3 you will have X * Y. Then, for each match between table3 and Table2 will have the same impact... Y * Z... So your result for just one possible ID in table 1 can have X * Y * Z records.
现在，回答你的问题。作为笛卡尔积的结果，您得到了大量计数。对于在 Table3 中匹配的 Table1 中的每条记录，您将有 X * Y。然后，对于 table3 和 Table2 之间的每个匹配都会产生相同的影响...... Y * Z ... 所以你的结果只有表 1 中的一个可能的 ID可以有 X * Y * Z 记录。
This is based on not knowing how the normalization or content is for your tables... if the key is a PRIMARY key or not..
这是基于不知道您的表的规范化或内容如何......如果键是主键或不是......
Ex:
Table 1
DiffKey Other Val
1 X
1 Y
1 Z
Table 3
DiffKey Key Key2 Tbl3 Other
1 2 6 V
1 2 6 X
1 2 6 Y
1 2 6 Z
Table 2
Key Key2 Other Val
2 6 a
2 6 b
2 6 c
2 6 d
2 6 e
So, Table 1 joining to Table 3 will result (in this scenario) with 12 records (each in 1 joined with each in 3). Then, all that again times each matched record in table 2 (5 records)... total of 60 ( 3 tbl1 * 4 tbl3 * 5 tbl2 )count would be returned.
因此，表 1 连接到表 3 将导致（在这种情况下）有 12 条记录（每条记录与每条记录相连 3 条）。然后，所有这些再次对表 2 中的每个匹配记录（5 条记录）进行计时......总共将返回 60（3 tbl1 * 4 tbl3 * 5 tbl2）计数。
So, now, take that and expand based on your 1000's of records and you see how a messed-up structure could choke a cow (so-to-speak) and kill performance.
所以，现在，根据你的 1000 条记录进行扩展，你会看到一个混乱的结构如何扼杀母牛（可以这么说）并扼杀性能。
SELECT
COUNT(*)
FROM
Table1
INNER JOIN Table3
ON Table1.DifferentKey = Table3.DifferentKey
INNER JOIN Table2
ON Table3.Key =Table2.Key
AND Table3.Key2 = Table2.Key2
回答by canon
Since you've already received help on the query, I'll take a poke at your syntax question:
由于您已经获得了有关查询的帮助，我将研究一下您的语法问题：
The first query employs some lesser-known ANSI SQL syntax which allows you to nest joins between the joinand onclauses. This allows you to scope/tier your joins and probably opens up a host of other evil, arcane things.
第一个查询使用了一些鲜为人知的 ANSI SQL 语法，它允许您在joinandon子句之间嵌套连接。这允许您对连接进行范围/分层，并可能打开许多其他邪恶的、神秘的事物。
Now, while a nested join cannot refer any higher in the join hierarchy than its immediate parent, joins above it or outside of its branch canrefer to it... which is precisely what this ugly little guy is doing:
现在，虽然嵌套连接不能在任何参考更高层次参与比其直接父，加入它或它的分支之外的上面可以参考一下吧......这恰恰是这个丑陋的小家伙正在做：
select
count(*)
from Table1 as t1
join Table2 as t2
join Table3 as t3
on t2.Key = t3.Key -- join #1
and t2.Key2 = t3.Key2
on t1.DifferentKey = t3.DifferentKey -- join #2
This looks a little confusing because join #2 is joining t1 to t2 without specifically referencing t2... however, it references t2 indirectly via t3 -as t3 is joined to t2 in join #1. While that may work, you may find the following a bit more (visually) linear and appealing:
这看起来有点令人困惑，因为 join #2 在没有特别引用 t2 的情况下将 t1 连接到 t2……但是，它通过 t3 间接引用了 t2 - 因为 t3 在 join #1 中连接到 t2。虽然这可能会奏效，但您可能会发现以下内容更具（视觉上）线性和吸引力：
select
count(*)
from Table1 as t1
join Table3 as t3
join Table2 as t2
on t2.Key = t3.Key -- join #1
and t2.Key2 = t3.Key2
on t1.DifferentKey = t3.DifferentKey -- join #2
Personally, I've found that nesting in this fashion keeps my statements tidy by outlining each tier of the relationship hierarchy. As a side note, you don't need to specify inner. joinis implicitly innerunless explicitly marked otherwise.
就我个人而言，我发现以这种方式嵌套可以通过概述关系层次结构的每一层来保持我的语句整洁。作为旁注，您不需要指定inner。除非另有明确标记，否则join是隐式内部的。
