SQL 内连接 vs Where

Inner join vs Where

提问by juan

Is there a difference in performance (in oracle) between

之间的性能（在oracle中）是否存在差异

Select * from Table1 T1 
Inner Join Table2 T2 On T1.ID = T2.ID

And

和

Select * from Table1 T1, Table2 T2 
Where T1.ID = T2.ID

?

?

采纳答案by kiewic

No! The same execution plan, look at these two tables:

不！同样的执行计划，看这两张表：

CREATE TABLE table1 (
  id INT,
  name VARCHAR(20)
);

CREATE TABLE table2 (
  id INT,
  name VARCHAR(20)
);

The execution plan for the query using the inner join:

使用内连接的查询的执行计划：

-- with inner join

EXPLAIN PLAN FOR
SELECT * FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id;

SELECT *
FROM TABLE (DBMS_XPLAN.DISPLAY);

-- 0 select statement
-- 1 hash join (access("T1"."ID"="T2"."ID"))
-- 2 table access full table1
-- 3 table access full table2

And the execution plan for the query using a WHERE clause.

以及使用 WHERE 子句的查询的执行计划。

-- with where clause

EXPLAIN PLAN FOR
SELECT * FROM table1 t1, table2 t2
WHERE t1.id = t2.id;

SELECT *
FROM TABLE (DBMS_XPLAN.DISPLAY);

-- 0 select statement
-- 1 hash join (access("T1"."ID"="T2"."ID"))
-- 2 table access full table1
-- 3 table access full table2

回答by Craig Trader

If the query optimizer is doing its job right, there should be no difference between those queries. They are just two ways to specify the same desired result.

如果查询优化器的工作正常，那么这些查询之间应该没有区别。它们只是指定相同所需结果的两种方法。

回答by Nescio

They should be exactly the same. However, as a coding practice, I would rather see the Join. It clearly articulates your intent,

它们应该完全相同。但是，作为一种编码实践，我更愿意看到 Join。它清楚地表达了你的意图，

回答by user21241

Using JOINmakes the code easier to read, since it's self-explanatory.

使用JOIN使代码更易于阅读，因为它是不言自明的。

There's no difference in speed(I have just tested it) and the execution plan is the same.

速度没有区别（我刚刚测试过）并且执行计划是相同的。

回答by HLGEM

I don't know about Oracle but I know that the old syntax is being deprecated in SQL Server and will disappear eventually. Before I used that old syntax in a new query I would check what Oracle plans to do with it.

我不了解 Oracle，但我知道旧语法在 SQL Server 中已被弃用，最终会消失。在我在新查询中使用旧语法之前，我会检查 Oracle 计划用它做什么。

I prefer the newer syntax rather than the mixing of the join criteria with other needed where conditions. In the newer syntax it is much clearer what creates the join and what other conditions are being applied. Not really a big problem in a short query like this, but it gets much more confusing when you have a more complex query. Since people learn on the basic queries, I would tend to prefer people learn to use the join syntax before they need it in a complex query.

我更喜欢较新的语法，而不是将连接标准与其他需要的 where 条件混合在一起。在较新的语法中，创建连接的内容和应用的其他条件更加清晰。在像这样的简短查询中并不是一个大问题，但是当您有一个更复杂的查询时，它会变得更加混乱。由于人们学习基本查询，我倾向于更喜欢人们在复杂查询中需要它之前学习使用连接语法。

And again I don't know Oracle specifically, but I know the SQL Server version of the old style left join is flawed even in SQL Server 2000 and gives inconsistent results (sometimes a left join sometimes a cross join), so it should never be used. Hopefully Oracle doesn't suffer the same issue, but certainly left and right joins can be mcuh harder to properly express in the old syntax.

再说一次，我并不特别了解 Oracle，但我知道旧式左联接的 SQL Server 版本即使在 SQL Server 2000 中也有缺陷，并给出不一致的结果（有时左联接有时是交叉联接），所以它永远不应该是用过的。希望 Oracle 不会遇到同样的问题，但肯定左连接和右连接在旧语法中更难正确表达。

Plus it has been my experience (and of course this is strictly a personal opinion, you may have differnt experience) that developers who use the ANSII standard joins tend to have a better understanding of what a join is and what it means in terms of getting data out of the database. I belive that is becasue most of the people with good database understanding tend to write more complex queries and those seem to me to be far easier to maintain using the ANSII Standard than the old style.

此外，根据我的经验（当然，这完全是个人意见，您可能有不同的经验），使用 ANSII 标准联接的开发人员往往对联接是什么以及它在获取方面意味着什么有更好的理解数据库外的数据。我相信这是因为大多数对数据库有良好理解的人倾向于编写更复杂的查询，而且在我看来，使用 ANSII 标准比旧样式更容易维护。

回答by Chris Gill

[For a bonus point...]

[为了加分...]

Using the JOIN syntax allows you to more easily comment out the join as its all included on one line. This canbe useful if you are debugging a complex query

使用 JOIN 语法可以更轻松地注释掉连接，因为它全部包含在一行中。这可以，如果你正在调试一个复杂的查询是有用的

As everyone else says, they are functionally the same, however the JOIN is more clear of a statement of intent. It therefore mayhelp the query optimiser either in current oracle versions in certain cases (I have no idea if it does), it mayhelp the query optimiser in future versions of Oracle (no-one has any idea), or it mayhelp if you change database supplier.

正如其他人所说，它们在功能上是相同的，但是 JOIN 更清楚地表达了意图。因此，它可以帮助查询优化器或者在某些情况下，当前的Oracle版本（我有，如果它确实不知道），它可以帮助甲骨文（没有人有任何想法）的未来版本的查询优化器，也可以帮助，如果您更改数据库供应商。

回答by David Aldridge

They're logically identical, but in the earlier versions of Oracle that adopted ANSI syntax there were often bugs with it in more complex cases, so you'll sometimes encounter resistance from Oracle developers when using it.

它们在逻辑上是相同的，但是在采用 ANSI 语法的早期版本的 Oracle 中，在更复杂的情况下经常会出现错误，因此在使用它时有时会遇到 Oracle 开发人员的阻力。

回答by stili

The performance should be identical, but I would suggest using the join-version due to improved clarity when it comes to outer joins.

性能应该是相同的，但我建议使用 join-version，因为在涉及外连接时提高了清晰度。

Also unintentional cartesian products can be avoided using the join-version.

使用连接版本也可以避免无意的笛卡尔积。

A third effect is an easier to read SQL with a simpler WHERE-condition.

第三个效果是使用更简单的 WHERE 条件更容易阅读 SQL。

回答by cheduardo

Don’t forget that in Oracle, provided the join key attributes are named the same in both tables, you can also write this as:

不要忘记，在 Oracle 中，如果两个表中的连接键属性命名相同，您也可以将其写为：

select *
from Table1 inner join Table2 using (ID);

This also has the same query plan, of course.

当然，这也有相同的查询计划。

回答by abrittaf

In a scenario where tables are in 3rd normal form, joins between tables shouldn't change. I.e. join CUSTOMERS and PAYMENTS should always remain the same.

在表为第三范式的情况下，表之间的连接不应更改。即加入客户和付款应始终保持不变。

However, we should distinguish joinsfrom filters. Joins are about relationships and filters are about partitioning a whole.

但是，我们应该区分连接和过滤器。联接是关于关系的，而过滤器是关于分割整体。

Some authors, referring to the standard (i.e. Jim Melton; Alan R. Simon (1993). Understanding The New SQL: A Complete Guide. Morgan Kaufmann. pp. 11–12. ISBN 978-1-55860-245-8.), wrote about benefits to adopt JOIN syntax over comma-separated tables in FROM clause.

一些作者，参考标准（即 Jim Melton；Alan R. Simon (1993)。Understanding The New SQL: A Complete Guide. Morgan Kaufmann. pp. 11–12. ISBN 978-1-55860-245-8.） ，写了在 FROM 子句中对逗号分隔表采用 JOIN 语法的好处。

I totally agree with this point of view.

我完全同意这个观点。

There are several ways to write SQL and achieve the same results but for many of those who do teamwork, source code legibility is an important aspect, and certainly separate how tables relate to each other from specific filters was a big leap in sense of clarifying source code.

有多种编写 SQL 并获得相同结果的方法，但对于许多进行团队合作的人来说，源代码易读性是一个重要方面，当然，将表与特定过滤器的相互关联方式分开是澄清源意义的一大飞跃代码。