SQL Oracle JOIN 3 表 - 哪种方式有效或有什么区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4892267/
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
Oracle JOIN 3 tables - Which way is efficient or is there any difference at all
提问by Vanchinathan Chandrasekaran
I can see people doing joins in different ways
我可以看到人们以不同的方式加入
select a.acc, b.acc, c.acc from a, b,c where a.acc=b.acc and c.acc = a.acc;
从 a、b、c 中选择 a.acc、b.acc、c.acc，其中 a.acc=b.acc 和 c.acc = a.acc；
and
和
select a.acc, b.acc, c.acc JOIN on a.acc=b.acc JOIN on c.acc = a.acc;
选择 a.acc, b.acc, c.acc JOIN on a.acc=b.acc JOIN on c.acc = a.acc;
Is there any difference? I suppose not.
有什么区别吗？我想不是。
回答by Mark Byers
In terms of performance there is no difference.
在性能方面没有区别。
I prefer the second approach for maintainability. It is more explicit which conditions are used to join which tables and it is easier to see whether or not you have missed out a join condition.
我更喜欢第二种方法的可维护性。使用哪些条件连接哪些表更加明确，并且更容易查看是否遗漏了连接条件。
回答by Justin Cave
In addition to Mark's point that using the latter syntax helps ensure that you don't inadvertently miss a join condition, one nice thing about the SQL 99 syntax is that if your column naming convention is that the name of the column in the parent and child table matches, you can use the USING clause rather than the ON clause, i.e.
除了 Mark 的观点，即使用后一种语法有助于确保您不会无意中错过连接条件，SQL 99 语法的一个好处是，如果您的列命名约定是父级和子级中的列名表匹配，您可以使用 USING 子句而不是 ON 子句，即
SELECT <<list of columns>>
FROM a JOIN b USING( acc )
JOIN c USING( acc )
This can improve the readability of the code and decrease the probability that you introduce errors by inadvertently joining tables incorrectly. If I had a nickel for every time I accidentally wrote something like
这可以提高代码的可读性，并降低由于无意中错误地连接表而引入错误的可能性。如果我每次不小心写了一些像
SELECT <<list of columns>>
FROM a,
a_to_b,
b
WHERE a.a_id = a_to_b.a_id
AND b.b_id = a_to_b.a_id -- a_to_b.**a_id** rather than a_to_b.**b_id**
when I really meant
当我真的想
SELECT <<list of columns>>
FROM a,
a_to_b,
b
WHERE a.a_id = a_to_b.a_id
AND b.b_id = a_to_b.b_id
I'd be a rich man. Or at least have enough for a good sushi dinner.
我会是个有钱人。或者至少有足够的寿司晚餐。
Most of the time, of course, it's immediately obvious that you've done something wrong because the data is completely screwy but it occasionally happens that the results are sufficiently close that it's not immediately obvious that you've done something wrong and it's not until much later that you discover the bug and track down the culprit query. It's basically impossible to make that sort of mistake if you write the query with the USING clause
当然，在大多数情况下，很明显你做错了什么，因为数据完全是乱七八糟的，但偶尔会发生结果非常接近，以至于你做错了什么并不是很明显，直到很久以后，您才发现错误并追踪罪魁祸首的查询。如果您使用 USING 子句编写查询，则基本上不可能犯这种错误
SELECT <<list of columns>>
FROM a JOIN a_to_b USING (a_id)
JOIN b USING (b_id)
回答by René Nyffenegger
if your second select is to read select a.acc, b.acc, c.acc from a JOIN b on a.acc=b.acc JOIN c on c.acc = a.acc;then there is no difference.
如果您的第二个选择是阅读select a.acc, b.acc, c.acc from a JOIN b on a.acc=b.acc JOIN c on c.acc = a.acc;，则没有区别。
