SQL JOIN：USING、ON 或 WHERE 之间有区别吗？

SQL JOIN: is there a difference between USING, ON or WHERE?

提问by Yochai Timmer

I was wondering if there is any difference in the way SQL performs on these join statements:

我想知道 SQL 对这些连接语句的执行方式是否有任何不同：

SELECT * FROM a,b WHERE a.ID = b.ID

SELECT * FROM a JOIN b ON a.ID = b.ID

SELECT * FROM a JOIN b USING(ID)

Is there a performance difference? Or algorithmic difference?

有性能差异吗？还是算法差异？

Or is it just syntactic sugar?

或者它只是语法糖？

回答by gbn

There is no difference in performance.

性能没有区别。

However, the first style is ANSI-89 and will get your legs broken in some shops. Including mine. The second style is ANSI-92 and is much clearer.

然而，第一种款式是 ANSI-89，在一些商店里会弄断你的腿。包括我的。第二种样式是 ANSI-92 并且更加清晰。

Examples:

例子：

Which is the JOIN, which is the filter?

哪个是JOIN，哪个是过滤器？

FROM T1,T2,T3....
WHERE T1.ID = T2.ID AND
     T1.foo = 'bar' AND T2.fish = 42 AND
     T1.ID = T3.ID

FROM T1 
   INNER JOIN T2 ON T1.ID = T2.ID
   INNER JOIN T3 ON T1.ID = T3.ID
WHERE
   T1.foo = 'bar' AND T2.fish = 42

If you have OUTER JOINs (=*, *=) then the 2nd style will work as advertised. The first most likely won't and is also deprecated in SQL Server 2005+

如果您有 OUTER JOINs ( =*, *=) 那么第二种样式将像宣传的那样工作。第一个很可能不会，并且在 SQL Server 2005+ 中也不推荐使用

The ANSI-92 style is harder to bollix too. With the older style you can easily end up with a Cartesian product (cross join) if you miss a condition. You'll get a syntax error with ANSI-92.

ANSI-92 样式也更难 bollix。使用较旧的样式，如果您错过某个条件，您可以轻松地得到笛卡尔积（交叉连接）。ANSI-92 会出现语法错误。

Edit: Some more clarification

编辑：更多说明

• The reason for not using "join the where" (implicit) is the dodgy results with outer joins.
• If you use explicit OUTER JOINs + implicit INNER JOINs you'll still get dodgy results + you have inconsistency in usage

• 不使用“join the where”（隐式）的原因是外部联接的结果不可靠。
• 如果你使用显式的 OUTER JOINs + 隐式的 INNER JOINs 你仍然会得到狡猾的结果 + 你在使用上不一致

It isn't just syntax: it's about having a semantically correctquery

这不仅仅是语法：它是关于有一个语义正确的查询

Edit, Dec 2011

编辑，2011 年 12 月

SQL Server logicalquery processing orderis FROM, ON, JOIN, WHERE...

SQL Server逻辑查询处理顺序为 FROM、ON、JOIN、WHERE...

So if you mix "implicit WHERE inner joins" and "explicit FROM outer joins" you most likely won't get expected results because the query is ambiguous...

因此，如果您混合使用“隐式 WHERE 内连接”和“显式 FROM 外连接”，您很可能不会得到预期的结果，因为查询不明确......

回答by Pete M

The difference is readability and maintainability. SELECT * FROM a JOIN b ON a.ID = b.IDconveys your exact intent, all in the same place.

区别在于可读性和可维护性。 SELECT * FROM a JOIN b ON a.ID = b.ID在同一个地方传达您的确切意图。

I won't say definitively since I haven't gotten under the hood of the last query optimizer, but I'm pretty confident you're looking at a trivial difference in performance, if any.

我不会明确地说，因为我还没有深入了解最后一个查询优化器，但我非常有信心您看到的是性能上的微小差异（如果有的话）。

回答by JonH

I despise when you force a join by using WHERE. It just doesn't to me look right, a dirty hack. Proper ANSI join is to use ON:

当您使用WHERE. 它只是在我看来不正确，一个肮脏的黑客。正确的 ANSI 连接是使用 ON：

SELECT 
    p.Product,
    o.Order
FROM 
    Product p
INNER JOIN
    Order o
ON
    o.OrderID = p.OrderID

Prefer using ONwhen joining and WHEREto filter results. Remember WHERE is one of the last things you will use besides grouping and order by where you want to filter your results. So you shouldn't join your tables using WHEREas it is much difficult to read.

ON加入时优先使用并WHERE过滤结果。记住 WHERE 是除了按要过滤结果的位置进行分组和排序之外，您将使用的最后一件事之一。所以你不应该使用连接你的表格，WHERE因为它很难阅读。

SELECT 
    p.Product,
    o.Order
FROM 
    Product p
INNER JOIN
    Order o
ON
    o.OrderID = p.OrderID
WHERE
    o.Category = 'IT'

In the end you (the developer) might not be around in the future so readability and maintainability will help the pour soul who has to take over your code :).

最后，您（开发人员）将来可能不会出现，因此可读性和可维护性将有助于必须接管您的代码的灵魂:)。

When I see developers use WHEREto join their tables it's usually an indication that they don't know enough T-SQL. That is my personal opinion.

当我看到开发人员WHERE用来连接他们的表时，这通常表明他们对 T-SQL 的了解不够。这是我个人的看法。

回答by Jason

See this

看到这个

INNER JOIN ON vs WHERE clause

INNER JOIN ON vs WHERE 子句

回答by scrappedcola

This is a duplicate of this SO question: Explicit vs implicit SQL joins. Generally I think the implicit (where version) is bad form and not as clear as the explicit (on version). I also think the implicit is being depreciated but not 100% on that one. The execution plan is the same for both though.

这是这个 SO 问题的副本：Explicit vsimplicit SQL joins。一般来说，我认为隐式（版本）是不好的形式，不像显式（版本）那样清晰。我还认为隐式正在贬值，但不是 100%。不过，两者的执行计划是相同的。

回答by Lukas Eder

No one has provided an answer about the USING(...)syntax yet.

还没有人提供有关USING(...)语法的答案。

While these two queries are equivalent logically, and also from the perspective of most modern optimisers:

虽然这两个查询在逻辑上是等效的，而且从大多数现代优化器的角度来看：

SELECT * FROM a, b WHERE a.id = b.id
SELECT * FROM a JOIN b ON a.id = b.id

This one has a slightly different semantics:

这个语义略有不同：

SELECT * FROM a JOIN b USING (id)

Assuming the following schema:

假设以下架构：

CREATE TABLE a (id int, a1 int, a2 int);
CREATE TABLE b (id int, b1 int, b2 int);

The first two queries will have their asterisks expanded to:

前两个查询的星号将扩展为：

SELECT a.id, a.a1, a.a2, b.id, b.a1, b.a2 FROM ...

Whereas the third query will have its asterisk expanded to:

而第三个查询将其星号扩展为：

SELECT coalesce(a.id, b.id) AS id, a.a1, a.a2, b.a1, b.a2 FROM ...

This is quite different for various reasons, including:

由于各种原因，这是完全不同的，包括：

• The number of projected columns is now 5 instead of 6. This could get in the way when you use UNIONor other set operations. Which you probably don't, with an asterisk.
• There is no longer a qualified (and duplicate) a.idor b.idcolumn, only an idcolumn. While PostgreSQL still allows for qualifying references to id(e.g. when needing to disambiguate them), Oracle for example doesn't.
• As a consequence, in the third query with the USING(...)syntax, projecting a.*or b.*is no longer possible in Oracle.

• 投影列的数量现在是 5 而不是 6。这可能会妨碍您使用UNION或其他设置操作。你可能不知道，带星号。
• 不再有限定（和重复）a.id或b.id列，只有一id列。虽然 PostgreSQL 仍然允许限定引用id（例如，当需要消除它们的歧义时），例如 Oracle 不允许。
• 因此，在具有USING(...)语法的第三个查询中，投影a.*orb.*不再可能在 Oracle 中。