就性能而言，NATURAL JOIN 是否比 SELECT FROM WHERE 更好？
可能的重复：
内连接与外连接
今天我和我的项目经理就笛卡尔积进行了辩论。他说“自然连接”在某种程度上比使用“从哪里选择”要好得多，因为后者会导致数据库引擎在内部执行笛卡尔积，但前者使用另一种方法来防止这种情况发生。据我所知，自然连接语法在性能或含义方面与“从何处选择”没有任何不同，我的意思是您可以根据自己的喜好使用其中任何一个。
SELECT * FROM table1,table2 WHERE table1.id=table2.id
SELECT * FROM table1 NATURAL JOIN table2
请详细说明第一个查询导致笛卡尔积，但第二个查询在某种程度上更智能
Possible Duplicate:
Inner join vs Where
Today I got into a debate with my project manager about Cartesian products. He says a 'natural join' is somehow much better than using 'select from where' because the later cause the db engine to internally perform a Cartesian product but the former uses another approach that prevents this. As far as I know, the natural join syntax is not any different in anyway than 'select from where' in terms of performance or meaning, I mean you can use either based on your taste.
SELECT * FROM table1,table2 WHERE table1.id=table2.id
SELECT * FROM table1 NATURAL JOIN table2
please elaborate about the first query causing a Cartesian product but the second one being somehow more smart
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
正确的方法应该是显式的，使用过滤器和连接分开
自然连接可能很简单并且“干净”，但更有可能是完全不可预测的......
编辑，2012年5月。
重复的接受答案不'实际上并没有回答 NATURAL JOIN。
这些链接进行了更详细的讨论。
tl;dr
性能不是问题：但您的查询应该可靠且可预测，而 NATURAL JOIN 当然不是。
根据这些链接，“JOIN in the WHERE”又名隐含的 JOIN 又名“笛卡尔”也是不好的（这同样适用于 Oracle 和 SQL Server）
The correct way should be explicit with filters and joins separated
NATURAL JOINS may be easy and "clean" but more likely to be utterly unpredictable...
Edit, May 2012.
The accepted answer for the duplicate doesn't actually answer NATURAL JOIN.
These links discuss in further detail.
tl;dr
Performance isn't the issue: but your queries should be reliable and predictable which NATURAL JOIN certainly isn't.
"JOIN in the WHERE" aka implied JOIN aka what you call "Cartesian" is also bad as per these links (the same applies to Oracle as well as SQL Server)
就性能而言，没有区别。它被讨论了一遍又一遍。谷歌搜索“join syntax oracle vs where”可以找到几篇不错的文章，其中包括 Alexander 引用的本网站上的一篇文章。
但是，使用 NATURAL JOIN 时要小心。它将选择常见的列，例如 createate 或 createuser 或您通常不关心加入的列，并且可能会导致问题。我强烈建议不要在生产中使用 NATURAL JOIN...只需使用 INNER JOIN 并指定列。
甚至 Tom 也同意 。
Performance-wise, there is no difference. Its been discussed over and over and over again. Googling for "join syntax oracle vs where" results in several good articles, including the one on this site referenced by Alexander.
However, be careful using a NATURAL JOIN. It will pick up on common columns like createdate or createuser or such that you normally really don't care about joining on and may cause problems. I highly recommended against NATURAL JOIN in production...just use INNER JOIN and specify the columns.
Even Tom agrees.
这取决于。
自然连接链接两个表中具有相同名称的所有列。如果表 1 和表 2 中仅有的两列同名，那么优化器应该对这两个查询进行相同的评估；另一方面，如果两个表中有两个以上具有相同名称的列（或根本没有），则正在执行完全不同的查询。
无论如何，笛卡尔积几乎总是（我很想说总是）比任何其他类型的联接表现更差，因为它将一个表的每条记录与另一表的每条记录联接起来。
您的经理区分臀大肌和尺骨上端的能力如何？
It depends.
A natural join links all columns in two tables with the same name. If the only two columns in tables 1 and 2 with the same name are ID, then the two queries should be evaluated identically by the optimiser; on the other hand, if there are more than two columns with the same name (or none at all) in the two tables, a completely different query is being performed.
In any case, a cartesian product will almost invariably (I'm tempted to say always) perform worse than any other type of join, as it joins every record of one table with every record of the other table.
How good is your manager at distinguishing his gluteus maximus from the upper end of his ulna?
首先要指出的是，数据库优化器以自己的方式解释语法。显然，每个产品都有所不同，但坦率地说，如果任何 DBMS 惩罚最常见的连接表机制，我会感到惊讶。
就术语而言，它是生成笛卡尔积的交叉连接。这与内部联接不同，并且会生成不同的结果集。
最后，自然连接是可怕的，实际上等待发生的错误。所有思想正常的人都应该避免使用它们。
First thing to point out is that database optimizers interpret syntax in their own way. Obviously each product varies but I would be frankly astonished if any DBMS penalised what is the commonest mechanism for joining tables.
With regards to terminology, it is a cross join which generates a cartesian product. That is different from an inner join, and would generate a different result set.
Finally, natural joins are horrible, literally bugs waiting to happen. They should be avoided by all right-thinking people.
我不会使用任何一种语法。您的查询表明内部联接，我将使用显式语法。您永远不应该使用隐式连接，它们可能会受到误解（这是意外的交叉连接还是您有意这样做？）和意外的交叉连接。您会使用 18 年前被更好语法取代的 C# 代码吗（实际上 18 年前 C# 还不存在，但我想您明白我在说什么）？那为什么还要使用过时的 SQL 代码呢？
隐式联接不仅是维护方面的问题，而且如果您尝试对外联接使用隐式联接语法，则可能会成为一个大问题，因为这种语法在某些数据库中无法正常工作，并且在至少一个数据库（SQL Server）中已被弃用。 ， 我知道。如果您需要在左联接中对表进行过滤，则根本无法使用隐式语法来执行此操作，因为它会将其转换为内部联接。
是的，您的代码可以工作，但这是一种糟糕的技术，您应该习惯于显式使用内部联接，以便您向未来的维护人员明确您的意图，并且在编写更复杂的查询时不会产生意外问题。如果使用显式语法不是您的第二天性，那么当您需要使用 if 来处理更复杂的事情时，您会真的很困难。
在 30 年的数据库查询过程中，我从未见过需要编写自然连接并且必须查找自然连接是什么，因此使用它并不比隐含连接更清晰。
I would not use either syntax. Your query indicates an inner join, I would use the explicit syntax for that. You should not be using implied joins ever, they are subject to misinterpretation (was that an accidental cross join or did you mean to do that?) and accidental cross joins. Would you use C# code that was replaced 18 years agao with a better syntax (well actually C# didn't exist 18 years ago, but I think you understand what I'm saying)? Then why are you using outdated SQL code?
Not only is the implied join a problem for maintenance but it can be a big problem if you try to use the implied join syntax for outer joins as that does not work correctly in some databases and is also deprecated in at least one database, SQL Server, I know. And if you have the need for a filter on the table in the left join, you can't do that with the implied syntax at all becasue it will convert it to an innner join.
Yes your code works but it is a poor technique and you should get used to using the inner join explicitly, so that you are making your intent clear to furture maintainers and so you don't create accidental problems as you write more complex queries. If using the explicit syntax is not second nature for you, you will really struggle when you need to use if for something more complicated.
I have never in 30 years of querying databases seen a need to write a natural join and had to look up what one was, so using that is not more clear than the implied join.