postgresql 当使用 LEFT（外部）连接时，连接中表的顺序是否重要？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5009573/
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 in a join matter, when LEFT (outer) joins are used?
提问by Radio Yerevan
I would like to confirm that the SQL query
我想确认 SQL 查询
SELECT ....
FROM apples,
oranges
LEFT JOIN kiwis ON kiwis.orange_id = oranges.id,
bananas
WHERE ....
is exactly equivalent to other permutations in the FROM subclause, like
完全等同于 FROM 子句中的其他排列，例如
SELECT ....
FROM oranges
LEFT JOIN kiwis ON kiwis.orange_id = oranges.id,
bananas,
apples
WHERE ....
or
或者
SELECT ....
FROM bananas,
apples,
oranges
LEFT JOIN kiwis ON kiwis.orange_id = oranges.id
WHERE ....
as long as the explicit LEFT JOIN between oranges and kiwis remains intact. From what I've read in various documents, the returned set should be exactly the same.
只要橙子和猕猴桃之间显式的 LEFT JOIN 保持不变。从我在各种文档中阅读的内容来看，返回的集合应该完全相同。
I'm really only concerned with the results of the query, not its performance in an actual database. (I'm using PostgreSQL 8.3, which AFAIK doesn't support optimizer hints about the join order, and will try to create an optimal query plan automatically).
我真的只关心查询的结果，而不是它在实际数据库中的性能。（我使用的是 PostgreSQL 8.3，AFAIK 不支持有关连接顺序的优化器提示，并将尝试自动创建最佳查询计划）。
回答by gbn
It is the same but it is ambiguous as hell with the implicitCROSS JOINs. Use explicit JOINS.
它是相同的，但它与隐含的CROSS JOIN一样含糊不清。使用显式连接。
If you are joining in the WHERE clause then the results maydiffer because joins and filters are mixed up.
如果您在 WHERE 子句中加入，则结果可能会有所不同，因为联接和过滤器混淆了。
SELECT ....
FROM apples a
JOIN
bananas b ON ...
JOIN
oranges o ON ...
LEFT JOIN
kiwis k ON k.orange_id = o.id
WHERE (filters only)
Notes:
笔记：
- INNER JOINS and CROSS JOINS are commutative and associative: order does not matter usually.
- OUTER JOINS are not, which you identified
- SQL is declarative: you tell the optimiser what you want, not how to do it. This removes JOIN order considerations (subject to the previous 2 items)
- INNER JOINS 和 CROSS JOINS 是可交换和关联的：顺序通常无关紧要。
- OUTER JOINS 不是，您确定的
- SQL 是声明性的：你告诉优化器你想要什么，而不是如何去做。这消除了 JOIN 顺序注意事项（以前 2 项为准）
回答by Greg Smith
The situation is summarized at Controlling the Planner with Explicit JOIN Clauses. Outer joins don't get reordered, inner ones can be. And you can force a particular optimizer order by dropping *join_collapse_limit* before running the query, and just putting things in the order you want them it. That's how you "hint" at the database in this area.
这种情况在使用显式 JOIN 子句控制规划器中进行了总结。外部连接不会重新排序，内部连接可以。并且您可以通过在运行查询之前删除 *join_collapse_limit* 来强制特定的优化器顺序，并且只需按照您希望的顺序放置内容即可。这就是您在该区域“提示”数据库的方式。
In general, you want to use EXPLAIN to confirm what order you're getting, and that can sometimes be used to visually confirm that two queries are getting the same plan.
通常，您希望使用 EXPLAIN 来确认您获得的订单，并且有时可以用来直观地确认两个查询正在获得相同的计划。
回答by Simon Hughes
I've done SQL for donkeys years, and in all my experience, the table order does not matter. The database will look at the query as a whole and create the optimal query plan. That is why database companies employ many people with PhD in query plan optimisations.
我已经做 SQL 多年了，根据我的经验，表顺序并不重要。数据库会将查询视为一个整体并创建最佳查询计划。这就是为什么数据库公司在查询计划优化方面雇佣了许多拥有博士学位的人。
The database vendor would commit commercial suicide if it optimised by the order in which you personally listed the SQL in your query.
如果数据库供应商按照您个人在查询中列出 SQL 的顺序进行优化，那么数据库供应商就会自杀。
