SQL 1: select * from t1 join t2 on t1.f1 = t2.f2
SQL 1:在t1.f1 = t2.f2上从t1加入t2中选择*
SQL 2: select * from t1,t2 where t1.f1 = t2.f2
SQL 2:从t1,t2中选择*,其中t1.f1 = t2.f2
The results that they return are same. Are there any differences between them? For example, in how the DBMS runs them, or in the query plan?
他们返回的结果是一样的。它们之间有什么不同吗?例如,在DBMS如何运行它们或在查询计划中?
4 个解决方案
#1
11
There is no operational difference between the two queries.
两个查询之间没有操作差异。
However, the explicit join notation is the better style to learn and use; leave the other for (as yet unchanged) legacy code.
但是,显式连接符号是更好的学习和使用方式;离开另一个(尚未更改)遗留代码。
#2
6
One is old style and one is new (ANSI) style. The main reason that I've found why you would want to use the new style is for standard support of outer joins. With the old style, outer joins are vendor-specific. New style has it standard:
一种是旧式,一种是新的(ANSI)式。我找到你想要使用新风格的原因的主要原因是外部连接的标准支持。使用旧样式,外部联接是特定于供应商的。新款有标准:
select * from t1 left outer join t2 on t1.f1 = t2.f2
In your example, SQL 1 is the new and SQL 2 is the old style, btw.
在您的示例中,SQL 1是新的,SQL 2是旧样式,顺便说一句。
#3
1
Basically, there are no difference between the two queries in operation.
基本上,两个查询在运行中没有区别。
However, both have same execution plan and have same cost that mean both query take equal time to execute(same performance).
但是,两者具有相同的执行计划并且具有相同的成本,这意味着两个查询都需要相同的执行时间(相同的性能)。
Use of join operator is a modern way.
使用join运算符是一种现代方法。
#4
1
The two are semantically equivalent (among other variations on the theme). One difference is that many users on * are very vocal in expressing their intolerant to 'old style' inner joins (your SQL 2), to the point where anyone posting them risks being downvoted in addition to being admonished in comments. You're also likely to see the term 'anti-pattern' applied, which is nonsense. I've not encountered this style intolerance outside of the SO community. In fact, 'old style' inner joins are very common in the SQL literature.
这两者在语义上是等价的(在主题的其他变体中)。一个区别是,*上的许多用户都表达了他们对“旧式”内部联接(您的SQL 2)不容忍的声音,除了在评论中被告诫之外,任何发布它们的人都有可能被低估。您也可能会看到应用“反模式”一词,这是无稽之谈。我没有在SO社区之外遇到这种风格的不宽容。实际上,“旧式”内连接在SQL文献中非常常见。
#1
11
There is no operational difference between the two queries.
两个查询之间没有操作差异。
However, the explicit join notation is the better style to learn and use; leave the other for (as yet unchanged) legacy code.
但是,显式连接符号是更好的学习和使用方式;离开另一个(尚未更改)遗留代码。
#2
6
One is old style and one is new (ANSI) style. The main reason that I've found why you would want to use the new style is for standard support of outer joins. With the old style, outer joins are vendor-specific. New style has it standard:
一种是旧式,一种是新的(ANSI)式。我找到你想要使用新风格的原因的主要原因是外部连接的标准支持。使用旧样式,外部联接是特定于供应商的。新款有标准:
select * from t1 left outer join t2 on t1.f1 = t2.f2
In your example, SQL 1 is the new and SQL 2 is the old style, btw.
在您的示例中,SQL 1是新的,SQL 2是旧样式,顺便说一句。
#3
1
Basically, there are no difference between the two queries in operation.
基本上,两个查询在运行中没有区别。
However, both have same execution plan and have same cost that mean both query take equal time to execute(same performance).
但是,两者具有相同的执行计划并且具有相同的成本,这意味着两个查询都需要相同的执行时间(相同的性能)。
Use of join operator is a modern way.
使用join运算符是一种现代方法。
#4
1
The two are semantically equivalent (among other variations on the theme). One difference is that many users on * are very vocal in expressing their intolerant to 'old style' inner joins (your SQL 2), to the point where anyone posting them risks being downvoted in addition to being admonished in comments. You're also likely to see the term 'anti-pattern' applied, which is nonsense. I've not encountered this style intolerance outside of the SO community. In fact, 'old style' inner joins are very common in the SQL literature.
这两者在语义上是等价的(在主题的其他变体中)。一个区别是,*上的许多用户都表达了他们对“旧式”内部联接(您的SQL 2)不容忍的声音,除了在评论中被告诫之外,任何发布它们的人都有可能被低估。您也可能会看到应用“反模式”一词,这是无稽之谈。我没有在SO社区之外遇到这种风格的不宽容。实际上,“旧式”内连接在SQL文献中非常常见。