当使用左（外）连接时，连接中表的顺序重要吗？

发布于 2024-10-18 02:51:14 字数 680 浏览 3 评论 0原文

我想确认 SQL 查询

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

与 FROM 子条款中的其他排列完全相同，例如

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 保持不变。从我在各种文档中读到的内容来看，返回的集合应该完全相同。

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

原文

I would like to confirm that the SQL query

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

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

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.

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

山有枢 2024-10-25 02:51:14

它是相同的，但是对于隐式 CROSS JOIN 来说它是非常模糊的。使用显式联接。

如果您在 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)

注意：

INNER JOINS 和 CROSS JOINS 是可交换的和关联的：顺序通常并不重要。
OUTER JOINS 则不然，您认为
SQL 是声明性的：您告诉优化器您想要什么，而不是如何做。这消除了 JOIN 顺序注意事项（受前 2 项限制）

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

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)

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)

回复收藏 0 原文