使用LEFT(外部)联接时，联接中表的顺序是否重要? [英] Does the order of tables in a join matter, when LEFT (outer) joins are used?

问题描述

我想确认SQL查询

SELECT ....
  FROM apples,
       oranges
       LEFT JOIN kiwis ON kiwis.orange_id = oranges.id,
       bananas
 WHERE ....

完全等同于FROM子句中的其他排列，例如

is exactly equivalent to other permutations in the FROM subclause, like

SELECT ....
  FROM oranges
       LEFT JOIN kiwis ON kiwis.orange_id = oranges.id,
       bananas,
       apples
 WHERE ....

或

SELECT ....
  FROM bananas,
       apples,
       oranges
       LEFT JOIN kiwis ON kiwis.orange_id = oranges.id
 WHERE ....

只要橙子和奇异果之间的显性LEFT JOIN保持不变即可.根据我在各种文档中所阅读的内容，返回的集合应该完全相同.

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.

我实际上只关心查询的结果，而不关心它在实际数据库中的性能. (我使用的是PostgreSQL 8.3，AFAIK不支持有关连接顺序的优化程序提示，并且会尝试自动创建最佳查询计划.)

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).

推荐答案

相同，但是与隐式交叉联接一样，它与地狱模棱两可.使用显式JOINS.

It is the same but it is ambiguous as hell with the implicit CROSS JOINs. Use explicit JOINS.

如果您要加入WHERE子句，则结果 可能会有所不同，因为联接和过滤器混合在一起.

If you are joining in the WHERE clause then the results may differ because joins and filters are mixed up.

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)

注意:

• 内部联接和交叉联接是可交换的和关联的:顺序通常并不重要.
• 您未确定的外部联接
• SQL是声明性的:您告诉优化器您想要什么，而不是如何去做.这消除了JOIN订单的注意事项(受前2个项目的限制)

这篇关于使用LEFT(外部)联接时，联接中表的顺序是否重要?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！