SQL JOIN:USING、ON 或 WHERE 之间有区别吗? [英] SQL JOIN: is there a difference between USING, ON or WHERE?

问题描述

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

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?

或者只是语法糖?

推荐答案

性能上没有区别.

但是，第一种样式是 ANSI-89，在某些商店会弄断您的腿.包括我的.第二种风格是ANSI-92，清晰多了.

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.

示例:

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

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

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

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+

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

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.

更多说明

• 不使用join the where"(隐式)的原因是外连接的结果不可靠.
• 如果您使用显式 OUTER JOINs + 隐式 INNER JOINs，您仍然会得到不可靠的结果 + 使用不一致

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

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

编辑，2011 年 12 月

Edit, Dec 2011

SQL Server逻辑查询处理顺序是FROM，ON, 加入, 哪里...

SQL Server logical query processing order is FROM, ON, JOIN, WHERE...

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

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屋！