SQL 中的连接顺序重要吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9614922/
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
Does the join order matter in SQL?
提问by Just a learner
Disregarding performance, will I get the same result from query A and B below? How about C and D?
不考虑性能，我会从下面的查询 A 和 B 得到相同的结果吗？C和D呢？
-- A
select *
from a left join b
on <blahblah>
left join c
on <blahblan>
-- B
select *
from a left join c
on <blahblah>
left join b
on <blahblan>
-- C
select *
from a join b
on <blahblah>
join c
on <blahblan>
-- D
select *
from a join c
on <blahblah>
join b
on <blahblan>
回答by ypercube??
For INNERjoins, no, the order doesn't matter. The queries will return same results, as long as you change your selects from SELECT *to SELECT a.*, b.*, c.*.
对于INNER连接，不，顺序无关紧要。该查询将返回相同的结果，只要你改变你的选择SELECT *来SELECT a.*, b.*, c.*。
For (LEFT, RIGHTor FULL) OUTERjoins, yes, the order matters - and (updated) things are much more complicated.
对于 ( LEFT, RIGHTor FULL)OUTER连接，是的，顺序很重要 - 而（更新）事情要复杂得多。
First, outer joins are not commutative, so a LEFT JOIN bis not the same as b LEFT JOIN a
首先，外连接不是可交换的，所以a LEFT JOIN b与b LEFT JOIN a
Outer joins are not associative either, so in your examples which involve both (commutativity and associativity) properties:
外连接也不是关联的，因此在您的示例中同时涉及（交换性和关联性）属性：
a LEFT JOIN b
ON b.ab_id = a.ab_id
LEFT JOIN c
ON c.ac_id = a.ac_id
is equivalent to:
相当于：
a LEFT JOIN c
ON c.ac_id = a.ac_id
LEFT JOIN b
ON b.ab_id = a.ab_id
but:
但：
a LEFT JOIN b
ON b.ab_id = a.ab_id
LEFT JOIN c
ON c.ac_id = a.ac_id
AND c.bc_id = b.bc_id
is not equivalent to:
不等于：
a LEFT JOIN c
ON c.ac_id = a.ac_id
LEFT JOIN b
ON b.ab_id = a.ab_id
AND b.bc_id = c.bc_id
Another (hopefully simpler) associativity example. Think of this as (a LEFT JOIN b) LEFT JOIN c:
另一个（希望更简单）关联性示例。把它想象成(a LEFT JOIN b) LEFT JOIN c：
a LEFT JOIN b
ON b.ab_id = a.ab_id -- AB condition
LEFT JOIN c
ON c.bc_id = b.bc_id -- BC condition
This is equivalentto a LEFT JOIN (b LEFT JOIN c):
这等同于a LEFT JOIN (b LEFT JOIN c)：
a LEFT JOIN
b LEFT JOIN c
ON c.bc_id = b.bc_id -- BC condition
ON b.ab_id = a.ab_id -- AB condition
only because we have "nice" ONconditions. Both ON b.ab_id = a.ab_idand c.bc_id = b.bc_idare equality checks and do not involve NULLcomparisons.
只是因为我们有“好”的ON条件。这两个ON b.ab_id = a.ab_id和c.bc_id = b.bc_id是平等的检查，不涉及NULL比较。
You can even have conditions with other operators or more complex ones like: ON a.x <= b.xor ON a.x = 7or ON a.x LIKE b.xor ON (a.x, a.y) = (b.x, b.y)and the two queries would still be equivalent.
您甚至可以使用其他运算符或更复杂的条件，例如：ON a.x <= b.xorON a.x = 7或ON a.x LIKE b.xorON (a.x, a.y) = (b.x, b.y)并且这两个查询仍然是等效的。
If however, any of these involved IS NULLor a function that is related to nulls like COALESCE(), for example if the condition was b.ab_id IS NULL, then the two queries would not be equivalent.
但是，如果涉及任何这些IS NULL或与空值相关的函数，COALESCE()例如，如果条件为b.ab_id IS NULL，则这两个查询将不等效。
回答by Diego
for regular Joins, it doesn't. TableA join TableBwill produce the same execution plan as TableB join TableA(so your C and D examples would be the same)
对于常规连接，它没有。TableA join TableB将产生相同的执行计划TableB join TableA（因此您的 C 和 D 示例将相同）
for left and right joins it does. TableA left Join TableBis different than TableB left Join TableA, BUT its the same than TableB right Join TableA
对于左右连接，它确实如此。TableA left Join TableB与 不同TableB left Join TableA，但与TableB right Join TableA
回答by Teo J.
If you try joining C on a field from B before joining B, i.e.:
如果您在加入 B 之前尝试在来自 B 的字段上加入 C，即：
SELECT A.x, A.y, A.z FROM A
INNER JOIN C
on B.x = C.x
INNER JOIN b
on A.x = B.x
your query will fail, so in this case the order matters.
您的查询将失败，因此在这种情况下，顺序很重要。
回答by Saumyojit Das
Oracle optimizerchooses join order of tables for inner join. Optimizer chooses the join order of tables only in simple FROM clauses . U can check the oracle documentation in their website. And for the left, right outer join the most voted answer is right. The optimizer chooses the optimal join order as well as the optimal index for each table. The join order can affect which index is the best choice. The optimizer can choose an index as the access path for a table if it is the inner table, but not if it is the outer table (and there are no further qualifications).
Oracle 优化器为内连接选择表的连接顺序。优化器仅在简单的 FROM 子句中选择表的连接顺序。你可以在他们的网站上查看 oracle 文档。而对于左、右外连接，投票最多的答案是正确的。优化器为每个表选择最佳连接顺序以及最佳索引。连接顺序会影响哪个索引是最佳选择。如果是内表，优化器可以选择一个索引作为表的访问路径，但如果是外表，则不能选择索引作为访问路径（并且没有进一步的限定）。
The optimizer chooses the join order of tables only in simple FROM clauses. Most joins using the JOIN keyword are flattened into simple joins, so the optimizer chooses their join order.
优化器仅在简单的 FROM 子句中选择表的连接顺序。大多数使用 JOIN 关键字的连接被扁平化为简单连接，因此优化器选择它们的连接顺序。
The optimizer does not choose the join order for outer joins; it uses the order specified in the statement.
优化器不选择外连接的连接顺序；它使用语句中指定的顺序。
When selecting a join order, the optimizer takes into account: The size of each table The indexes available on each table Whether an index on a table is useful in a particular join order The number of rows and pages to be scanned for each table in each join order
选择连接顺序时，优化器会考虑： 每个表的大小 每个表上可用的索引 表上的索引在特定连接顺序中是否有用 每个表中要扫描的行数和页数加入顺序
