SQL JOIN：USING，ON或WHERE有区别吗？ [英] SQL JOIN: is there a difference between USING, ON or WHERE?

问题描述

我想知道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）
  

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

还是只是句法糖？

解决方案

性能没有差别。

然而，第一种风格是ANSI-89，并且会在一些商店中破裂。包括我的第二种风格是ANSI-92，更清晰。

示例：

哪个是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 
  

如果您有OUTER JOIN（ = * ， = ）然后第二种风格将按照广告的方式工作。在SQL Server 2005 +中，第一个很有可能不会也被弃用。

ANSI-92风格也更难被套用。使用较老款式，如果您错过了条件，您可以轻松地使用笛卡尔积（Cross join）。您会收到ANSI-92的语法错误。

编辑：更多的澄清

• 没有使用加入其中（隐含）是外部连接的诡计结果。


• 如果您使用显式OUTER JOIN +隐式INNER JOIN，您仍然会得到诡异的结果+您的使用不一致

这不仅仅是语法：它是关于使用语义正确查询 p>

编辑，2011年12月

SQL Server 逻辑查询处理顺序是FROM，ON，JOIN，WHERE ...

因此，如果您混合隐含的WHERE内部联接和显式FROM外连接，则很有可能不会得到预期的结果，因为查询是不明确的...

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

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?

解决方案

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.

Examples:

Which is the JOIN, which is the filter?

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+

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.

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

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

Edit, Dec 2011

SQL Server logical query processing order is 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...

这篇关于SQL JOIN：USING，ON或WHERE有区别吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！