blocks|key|1773144|text|在性能方面，它们是完全相同的(至少在SQL+Server中是这样)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1773145|PS:请注意，从SQL+Server2005开始就不再推荐使用IMPLICIT+OUTER+JOIN语法。(仍支持问题中使用的IMPLICIT+INNER+JOIN语法)|offset|length|style|CODE|1773146|Deprecation+of+"Old+Style"+JOIN+Syntax:+Only+A+Partial+Thing|1773147|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.microsoft.com/en-us/archive/blogs/wardpond/deprecation-of-old-style-join-syntax-only-a-partial-thing^0|0|V|J|1R|J|0|0|1O|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@$D|T|E|U|F|G]|$D|V|E|W|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|X|8|@]|9|@$D|Y|E|Z|1|10]]|A|$]]|$1|J|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q]]]]

Performance wise, they are exactly the same (at least in SQL Server).

PS: Be aware that the <code>IMPLICIT OUTER JOIN</code> syntax is deprecated since SQL Server 2005. (The <code>IMPLICIT INNER JOIN</code> syntax as used in the question is still supported)

<a href="https://docs.microsoft.com/en-us/archive/blogs/wardpond/deprecation-of-old-style-join-syntax-only-a-partial-thing" rel="noreferrer">Deprecation of "Old Style" JOIN Syntax: Only A Partial Thing</a>

blocks|key|1773207|text|就我个人而言，我更喜欢连接语法，因为它使表的连接以及它们如何连接变得更加清晰。试着比较更大的SQL查询，当你从8个不同的表中选择时，你在where中有很多过滤。通过使用join语法，您可以将表连接的部分与筛选行的部分分开。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1773208|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Personally I prefer the join syntax as its makes it clearer that the tables are joined and how they are joined. Try compare larger SQL queries where you selecting from 8 different tables and you have lots of filtering in the where. By using join syntax you separate out the parts where the tables are joined, to the part where you are filtering the rows.

blocks|key|1773289|text|在一些数据库(特别是Oracle)上，连接的顺序可能会对查询性能产生巨大的影响(如果有两个以上的表)。在一个应用程序中，我们在某些情况下有两个数量级的差异。如果使用正确的提示语法，使用inner+join语法就可以对此进行控制。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1773290|您没有指定您正在使用的数据库，但概率建议您使用SQL+Server或MySQL，在那里没有真正的区别。|1773291|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

On some databases (notably Oracle) the order of the joins can make a huge difference to query performance (if there are more than two tables). On one application, we had literally two orders of magnitude difference in some cases. Using the inner join syntax gives you control over this - if you use the right hints syntax.

You didn't specify which database you're using, but probability suggests SQL Server or MySQL where there it makes no real difference.

blocks|key|1773340|text|@lomaxx:为了澄清，我非常确定SQL+Serv+2005支持上述两种语法。但是，不支持以下语法|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1773341|select+a.*,+b.*++
from+table+a,+table+b++
where+a.id+*=+b.id;|code-block|syntax|javascript|1773342|具体来说，不支持外部联接(*=)。|1773343|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

@lomaxx: Just to clarify, I'm pretty certain that both above syntax are supported by SQL Serv 2005. The syntax below is NOT supported however

<pre><code>select a.*, b.* 
from table a, table b 
where a.id *= b.id;
</code></pre>

Specifically, the outer join (*=) is not supported.

blocks|key|1558209|text|您给出的第一个答案使用了众所周知的ANSI+join语法，另一个答案是有效的，可以在任何关系数据库中使用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1558210|我同意grom的观点，即应该使用ANSI+join语法。正如他们所说，主要原因是为了清晰。没有使用带有许多谓词的where子句，而是让哪些条件用于联接表，哪些条件用于限制结果，而不是让where子句包含许多谓词，其中一些谓词连接表，另一些谓词限制ANSI连接语法返回的行。|1558211|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

The first answer you gave uses what is known as ANSI join syntax, the other is valid and will work in any relational database.

I agree with grom that you should use ANSI join syntax. As they said, the main reason is for clarity. Rather than having a where clause with lots of predicates, some of which join tables and others restricting the rows returned with the ANSI join syntax you are making it blindingly clear which conditions are being used to join your tables and which are being used to restrict the results.

blocks|key|1773466|text|正如Leigh+Caldwell所说，查询优化器可以根据功能上类似于相同SQL语句的内容生成不同的查询计划。有关这方面的进一步阅读，请查看以下两篇博客帖子：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1773467|One+posting+from+the+Oracle+Optimizer+Team|offset|length|1773468|Another+posting+from+the+"Structured+Data"+blog|1773469|我希望你会觉得这很有趣。|1773470|entityMap|0|LINK|mutability|MUTABLE|url|http://optimizermagic.blogspot.com/2007/12/outerjoins-in-oracle.html|1|http://structureddata.org/2008/02/18/ansi-outer-joins-and-lateral-views/^0|0|0|16|0|0|0|1B|1|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@$D|V|E|W|1|X]]|A|$]]|$1|F|3|G|5|6|7|Y|8|@]|9|@$D|Z|E|10|1|11]]|A|$]]|$1|H|3|I|5|6|7|12|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|13|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q]]|R|$5|M|N|O|A|$P|S]]]]

As Leigh Caldwell has stated, the query optimizer can produce different query plans based on what functionally looks like the same SQL statement. For further reading on this, have a look at the following two blog postings:-

<a href="http://optimizermagic.blogspot.com/2007/12/outerjoins-in-oracle.html" rel="nofollow noreferrer">One posting from the Oracle Optimizer Team</a>

<a href="http://structureddata.org/2008/02/18/ansi-outer-joins-and-lateral-views/" rel="nofollow noreferrer">Another posting from the "Structured Data" blog</a>

I hope you find this interesting.

blocks|key|1773533|text|第二种语法具有不必要的交叉联接的可能性:您可以将表添加到FROM部分，而不需要相应的WHERE子句。这被认为是有害的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1773534|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

The second syntax has the unwanted possibility of a cross join: you can add tables to the FROM part without corresponding WHERE clause. This is considered harmful.

blocks|key|1558341|text|在性能方面，这不会有任何不同。显式的连接语法对我来说似乎更清晰，因为它清楚地定义了from子句中的表之间的关系，并且不会弄乱where子句。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1558342|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Performance wise, it should not make any difference. The explicit join syntax seems cleaner to me as it clearly defines relationships between tables in the from clause and does not clutter up the where clause.

blocks|key|1773622|text|在MySQL+5.1.51上，两个查询具有相同的执行计划：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1773623|mysql>+explain+select+*+from+table1+a+inner+join+table2+b+on+a.pid+=+b.pid;
%2B----%2B-------------%2B-------%2B------%2B---------------%2B------%2B---------%2B--------------%2B------%2B-------%2B
%7C+id+%7C+select_type+%7C+table+%7C+type+%7C+possible_keys+%7C+key++%7C+key_len+%7C+ref++++++++++%7C+rows+%7C+Extra+%7C
%2B----%2B-------------%2B-------%2B------%2B---------------%2B------%2B---------%2B--------------%2B------%2B-------%2B
%7C++1+%7C+SIMPLE++++++%7C+b+++++%7C+ALL++%7C+PRIMARY+++++++%7C+NULL+%7C+NULL++++%7C+NULL+++++++++%7C++986+%7C+++++++%7C
%7C++1+%7C+SIMPLE++++++%7C+a+++++%7C+ref++%7C+pid+++++++++++%7C+pid++%7C+4+++++++%7C+schema.b.pid+%7C+++70+%7C+++++++%7C
%2B----%2B-------------%2B-------%2B------%2B---------------%2B------%2B---------%2B--------------%2B------%2B-------%2B
2+rows+in+set+(0.02+sec)

mysql>+explain+select+*+from+table1+a,+table2+b+where+a.pid+=+b.pid;
%2B----%2B-------------%2B-------%2B------%2B---------------%2B------%2B---------%2B--------------%2B------%2B-------%2B
%7C+id+%7C+select_type+%7C+table+%7C+type+%7C+possible_keys+%7C+key++%7C+key_len+%7C+ref++++++++++%7C+rows+%7C+Extra+%7C
%2B----%2B-------------%2B-------%2B------%2B---------------%2B------%2B---------%2B--------------%2B------%2B-------%2B
%7C++1+%7C+SIMPLE++++++%7C+b+++++%7C+ALL++%7C+PRIMARY+++++++%7C+NULL+%7C+NULL++++%7C+NULL+++++++++%7C++986+%7C+++++++%7C
%7C++1+%7C+SIMPLE++++++%7C+a+++++%7C+ref++%7C+pid+++++++++++%7C+pid++%7C+4+++++++%7C+schema.b.pid+%7C+++70+%7C+++++++%7C
%2B----%2B-------------%2B-------%2B------%2B---------------%2B------%2B---------%2B--------------%2B------%2B-------%2B
2+rows+in+set+(0.00+sec)|code-block|syntax|javascript|1773624|table1有166208行；table2大约有1,000行。|offset|length|style|CODE|1773625|这是一个非常简单的情况；它绝不能证明查询优化器在更复杂的情况下不会混淆并生成不同的计划。|1773626|entityMap^0|0|0|0|6|F|6|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]|$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|P|$]]

On MySQL 5.1.51, both queries have identical execution plans:

<pre><code>mysql&gt; explain select * from table1 a inner join table2 b on a.pid = b.pid;
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
| 1 | SIMPLE | b | ALL | PRIMARY | NULL | NULL | NULL | 986 | |
| 1 | SIMPLE | a | ref | pid | pid | 4 | schema.b.pid | 70 | |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
2 rows in set (0.02 sec)

mysql&gt; explain select * from table1 a, table2 b where a.pid = b.pid;
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
| 1 | SIMPLE | b | ALL | PRIMARY | NULL | NULL | NULL | 986 | |
| 1 | SIMPLE | a | ref | pid | pid | 4 | schema.b.pid | 70 | |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
2 rows in set (0.00 sec)
</code></pre>

<code>table1</code> has 166208 rows; <code>table2</code> has about 1000 rows.

This is a very simple case; it doesn't by any means prove that the query optimizer wouldn't get confused and generate different plans in a more complicated case.

blocks|key|1558503|text|根据我的经验，使用cross-join-with-where-子句语法通常会产生大脑受损的执行计划，尤其是在使用Microsoft+SQL产品时。例如，SQL+Server试图估计表行计数的方式是非常可怕的。使用inner语法可以在一定程度上控制查询的执行方式。因此，从实际的角度来看，考虑到当前数据库技术的返祖特性，您必须使用内连接。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1558504|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

In my experience, using the cross-join-with-a-where-clause syntax often produces a brain damaged execution plan, especially if you are using a Microsoft SQL product. The way that SQL Server attempts to estimate table row counts, for instance, is savagely horrible. Using the inner join syntax gives you some control over how the query is executed. So from a practical point of view, given the atavistic nature of current database technology, you have to go with the inner join.

blocks|key|1773815|text|基本上，两者之间的区别是一个是用旧的方式编写的，而另一个是用现代的方式编写的。就我个人而言，我更喜欢使用内部、左侧、外部和右侧定义的现代脚本，因为它们更具解释性，使代码更具可读性。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1773816|当处理内部连接时，在可读性上也没有真正的区别，但是，当处理左连接和右连接时，它可能会变得复杂，因为在旧方法中，你会得到这样的结果：|1773817|SELECT+*+
FROM+table+a,+table+b
WHERE+a.id+=+b.id+(%2B);|code-block|syntax|javascript|1773818|上面是左连接的老方法，而不是下面的方式：|1773819|SELECT+*+
FROM+table+a+
LEFT+JOIN+table+b+ON+a.id+=+b.id;|1773820|正如您可以看到的那样，现代的脚本编写方式使查询更具可读性。(顺便说一句，右连接也是如此，而外连接则稍微复杂一些)。|1773821|回到样板，对于SQL编译器来说，查询是如何编写的并没有区别，因为它以相同的方式处理它们。我在Oracle数据库中看到了两者的混合，有很多人在写东西，既有年长的，也有年轻的。同样，它归结为脚本的可读性和与您一起开发的团队的可读性。|1773822|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

Basically, the difference between the two is that one is written in the old way, while the other is written in the modern way. Personally, I prefer the modern script using the inner, left, outer, right definitions because they are more explanatory and makes the code more readable.

When dealing with inner joins there is no real difference in readability neither, however, it may get complicated when dealing with left and right joins as in the older method you would get something like this:

<pre><code>SELECT * 
FROM table a, table b
WHERE a.id = b.id (+);
</code></pre>

The above is the old way how a left join is written as opposed to the following:

<pre><code>SELECT * 
FROM table a 
LEFT JOIN table b ON a.id = b.id;
</code></pre>

As you can visually see, the modern way of how the script is written makes the query more readable. (By the way same goes for right joins and a little more complicated for outer joins).

Going back to the boiler plate, it doesn't make a difference to the SQL compiler how the query is written as it handles them in the same way. I've seen a mix of both in Oracle databases which have had many people writing into it, both elder and younger ones. Again, it boils down to how readable the script is and the team you are developing with.

Is there any efficiency difference in an explicit vs implicit inner join?
For example:

<pre><code>SELECT * FROM
table a INNER JOIN table b
ON a.id = b.id;
</code></pre>

vs.

<pre><code>SELECT a.*, b.*
FROM table a, table b
WHERE a.id = b.id;
</code></pre>

Explicit vs implicit SQL joins

Oracle数据库

SQL Server

数据库

编译器

MySQL

显式内连接和隐式内连接之间是否存在效率差异？例如：SELECT * FROMtable a INNER JOIN table bON a.id = b.id;与SELECT a.*, b.*FROM table a, table bWHERE a.id = b.id;

问显式SQL连接与隐式SQL连接
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问显式SQL连接与隐式SQL连接EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问显式SQL连接与隐式SQL连接
EN