oracle JOIN 的 ON 子句中引用的表的顺序是否重要？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/785583/
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 order of tables referenced in the ON clause of the JOIN matter?
提问by Even Mien
Does it matter which way I order the criteria in the ON clause for a JOIN?
我对 JOIN 的 ON 子句中的条件进行排序是否重要？
select a.Name, b.Status from a
inner join b
on a.StatusID = b.ID
versus
相对
select a.Name, b.Status from a
inner join b
on b.ID = a.StatusID
Is there any impact on performance? What if I had multiple criteria?
对性能有影响吗？如果我有多个标准怎么办？
Is one order more maintainable than another?
一个订单是否比另一个更易于维护？
采纳答案by Quassnoi
JOINorder can be forced by putting the tables in the right order in the FROMclause:
JOIN可以通过在FROM子句中按正确的顺序排列表来强制顺序：
MySQL has a special clause called
STRAIGHT_JOINwhich makes the order matter.This will use an index on
b.id:SELECT a.Name, b.Status FROM a STRAIGHT_JOIN b ON b.ID = a.StatusIDAnd this will use an index on
a.StatusID:SELECT a.Name, b.Status FROM b STRAIGHT_JOIN a ON b.ID = a.StatusIDOracle has a special hint
ORDEREDto enforce theJOINorder:This will use an index on
b.idor build a hash table onb:SELECT /*+ ORDERED */ * FROM a JOIN b ON b.ID = a.StatusIDAnd this will use an index on
a.StatusIDor build a hash table ona:SELECT /*+ ORDERED */ * FROM b JOIN a ON b.ID = a.StatusIDSQL Server has a hint called
FORCE ORDERto do the same:This will use an index on
b.idor build a hash table onb:SELECT * FROM a JOIN b ON b.ID = a.StatusID OPTION (FORCE ORDER)And this will use an index on
a.StatusIDor build a hash table ona:SELECT * FROM b JOIN a ON b.ID = a.StatusID OPTION (FORCE ORDER)PostgreSQL guys, sorry. Your TODO listsays:
Optimizer hints (not wanted)
Optimizer hints are used to work around problems in the optimizer. We would rather have the problems reported and fixed.
MySQL 有一个特殊的子句
STRAIGHT_JOIN，它使顺序很重要。这将在 上使用索引
b.id：SELECT a.Name, b.Status FROM a STRAIGHT_JOIN b ON b.ID = a.StatusID这将使用一个索引
a.StatusID：SELECT a.Name, b.Status FROM b STRAIGHT_JOIN a ON b.ID = a.StatusIDOracle 有一个特殊的提示
ORDERED来强制执行该JOIN命令：这将在 上使用索引
b.id或在 上构建哈希表b：SELECT /*+ ORDERED */ * FROM a JOIN b ON b.ID = a.StatusID这将使用索引
a.StatusID或构建哈希表a：SELECT /*+ ORDERED */ * FROM b JOIN a ON b.ID = a.StatusIDSQL Server 有一个提示调用
FORCE ORDER来做同样的事情：这将在 上使用索引
b.id或在 上构建哈希表b：SELECT * FROM a JOIN b ON b.ID = a.StatusID OPTION (FORCE ORDER)这将使用索引
a.StatusID或构建哈希表a：SELECT * FROM b JOIN a ON b.ID = a.StatusID OPTION (FORCE ORDER)PostgreSQL 伙计们，对不起。你的待办事项清单说：
优化器提示（不需要）
优化器提示用于解决优化器中的问题。我们宁愿报告和修复问题。
As for the order in the comparison, it doesn't matter in any RDBMS, AFAIK.
至于比较中的顺序，在任何情况下都无关紧要RDBMS，AFAIK。
Though I personally always try to estimate which column will be searched for and put this column in the left (for it to seem like an lvalue).
虽然我个人总是尝试估计将搜索哪一列并将此列放在左侧（因为它看起来像一个lvalue）。
See this answerfor more detail.
有关更多详细信息，请参阅此答案。
回答by ólafur Waage
No it does not.
不，不是的。
What i do (for readability) is your 2nd example.
我所做的（为了可读性）是你的第二个例子。
回答by David
No. The database should be determining the best execution plan based on the entire criteria, not creating it by looking at each item in sequence. You can confirm this by requesting the execution plan for both queries, you'll see they are the same (you'll find that even vastly different queries, as long as they ultimately specify the same logic, are often compiled into the same execution plan).
不。数据库应该根据整个标准确定最佳执行计划，而不是通过按顺序查看每个项目来创建它。您可以通过请求两个查询的执行计划来确认这一点，您会看到它们是相同的（您会发现即使是截然不同的查询，只要它们最终指定相同的逻辑，通常都会编译成相同的执行计划）。
回答by Peter Perhá?
No there is not. At the end of the day, you are really just evaluating whether a=b.
不，那里没有。归根结底，您实际上只是在评估是否 a=b。
And as the symmetric property of equality states:
正如平等的对称性质所说：
- For any quantities a and b, if a = b, then b = a.
- 对于任意量 a 和 b，如果 a = b，则 b = a。
so whether you check for (12)*=12or 12=(12)*makes logically no difference.
所以你是否检查(12)*=12或12=(12)*在逻辑上没有区别。
If values are equal, join, if not, don't. And whether you specify it as in your first example or the second, makes no difference.
如果值相等，则加入，如果不相等，则不加入。无论您在第一个示例还是第二个示例中指定它，都没有区别。
回答by Levite
As many have said: The order does not make a difference in result or performance.
正如许多人所说：顺序不会对结果或性能产生影响。
What I want to point out though is that LINQ to SQL only allows the first case!
不过我想指出的是，LINQ to SQL 只允许第一种情况！
Eg, following example works well, ...
例如，以下示例效果很好，...
var result = from a in db.a
join b in db.b on a.StatusID equals b.ID
select new { Name = a.Name, Status = b.Status }
... while this will throw errors in Visual Studio:
...虽然这会在 Visual Studio 中引发错误：
var result = from a in db.a
join b in db.b on b.ID equals a.StatusID
select new { Name = a.Name, Status = b.Status }
Which throws these compiler errors:
这会引发这些编译器错误：
- CS1937: The name 'name' is not in scope on the left side of 'equals'. Consider swapping the expressions on either side of 'equals'.
- CS1938: The name 'name' is not in scope on the right side of 'equals'. Consider swapping the expressions on either side of 'equals'.
- CS1937：名称“name”不在“equals”左侧的范围内。考虑交换'equals'两边的表达式。
- CS1938：名称“name”不在“equals”右侧的范围内。考虑交换'equals'两边的表达式。
Though not relevant in standard SQL coding, this might be a point to consider, when accustoming oneself to either one of those.
尽管在标准 SQL 编码中不相关，但在习惯其中任何一个时，这可能是需要考虑的一点。
回答by Sam Saffron
SqlServer contains an optimisation for situations far more complex than this.
SqlServer 包含针对比这更复杂的情况的优化。
If you have multiple criteria stuff is usually lazy evaluated (but I need to do a bit of research around edge cases if any.)
如果您有多个标准，通常会进行惰性评估（但如果有的话，我需要对边缘情况进行一些研究。）
For readability I usually prefer
为了可读性，我通常更喜欢
SELECT Name, Status FROM a
JOIN b
ON a.StatusID = b.ID
I think it makes better sense to reference the variable in the same order they were declared but its really a personal taste thing.
我认为以声明变量的相同顺序引用变量更有意义，但这确实是个人品味的事情。
回答by JeffO
The only reason I wouldn't use your second example:
我不会使用你的第二个例子的唯一原因：
select a.Name, b.Status
from a
inner join b
on b.ID = a.StatusID
Your user is more likely to come back and say 'Can I see all the a.name's even if they have no status records?' rather than 'Can I see all of b.status even if they don't have a name record?', so just to plan ahead for this example, I would use On a.StatusID = b.IDin anticipation of a LEFT Outer Join. This assumes you could have table 'a' record without 'b'.
您的用户更有可能回来说“即使他们没有状态记录，我也能看到所有的 a.name 吗？” 而不是“即使他们没有姓名记录，我能看到所有 b.status 吗？”，所以只是为了提前计划这个例子，我会使用On a.StatusID = b.IDLEFT Outer Join 的预期。这假设您可以拥有没有 'b' 的表 'a' 记录。
Correction: It won't change the result.
更正：它不会改变结果。
This is probably a moot point since users never want to change their requirements.
这可能是一个有争议的问题，因为用户永远不想改变他们的需求。
回答by DForck42
nope, doesn't matter. but here's an example to help make your queries more readable (at least to me)
不，没关系。但这里有一个例子可以帮助您提高查询的可读性（至少对我而言）
select a.*, b.*
from tableA a
inner join tableB b
on a.name=b.name
and a.type=b.type
each table reference is on a separate line, and each join criteria is on a separate line. the tabbing helps keep what belongs to what straight.
每个表引用在一个单独的行上，每个连接条件在一个单独的行上。标签有助于保持什么属于什么直线。
another thing i like to do is make my criteria in my on statements flow the same order as the table. so if a is first and then b, a will be on the left side and b on the right.
我喜欢做的另一件事是使我的 on 语句中的标准与表格的顺序相同。所以如果 a 是第一个然后是 b，a 将在左侧，b 在右侧。
回答by issalys2
ERROR: ON clause references tables to its right (php sqlite 3.2)
错误：ON 子句引用其右侧的表（php sqlite 3.2）
Replace this
替换这个
LEFT JOIN itm08 i8 ON i8.id= **cdd01.idcmdds** and i8.itm like '%ormit%'
LEFT JOIN **comodidades cdd01** ON cdd01.id_registro = u.id_registro
For this
为了这
LEFT JOIN **comodidades cdd01** ON cdd01.id_registro = u.id_registro
LEFT JOIN itm08 i8 ON i8.id= **cdd01.idcmdds** and i8.itm like '%ormit%'
