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

问题描述

我想确认 SQL 查询

I would like to confirm that the SQL query

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

推荐答案

它是相同的，但对于隐式 CROSS JOIN 来说是模棱两可的.使用显式连接.

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)

注意事项:

• INNER JOINS 和 CROSS JOINS 是可交换和关联的:顺序通常无关紧要.
• OUTER JOINS 不是您确定的
• SQL 是声明性的:你告诉优化器你想要什么，而不是如何去做.这消除了 JOIN 顺序注意事项(以前 2 项为准)

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