问题描述
下面的SQL的两位得到相同的结果
SELECT c.name, o.product
FROM customer c, order o
WHERE c.id = o.cust_id
AND o.value = 150
SELECT c.name, o.product
FROM customer c
INNER JOIN order o on c.id = o.cust_id
WHERE o.value = 150
我已经看到这两种样式在不同公司被用作标准.从我所看到的，第二个是大多数人在网上推荐的东西.除了风格，还有其他真正的原因吗?使用内部联接有时会带来更好的性能吗?
我注意到Ingres和Oracle开发人员倾向于使用第一种样式，而Microsoft SQL Server用户倾向于使用第二种样式，但这可能只是一个巧合.
感谢您的见解，我已经对此感到疑惑了.
由于我使用的术语不正确，因此我已将标题从"SQL内连接与笛卡尔乘积"更改了.感谢到目前为止的所有答复.
两个查询都是内部联接，并且是等效的.首先是较旧的做事方法，而JOIN语法的使用仅在引入SQL-92标准后才变得很普遍(我相信它是在较早的定义中，在此之前并未得到特别广泛的使用). /p>
强烈建议使用JOIN语法，因为它将WHERE子句中的联接逻辑与过滤逻辑分开.尽管JOIN语法实际上是内部联接的语法糖，但其优势在于外部联接，在外部联接中，旧的*语法会产生无法明确描述联接且解释依赖于实现的情况. [左| RIGHT] JOIN语法避免了这些陷阱，因此，为了保持一致性，在任何情况下都建议使用JOIN子句.
请注意，这两个示例都不是笛卡尔积.为此，您可以使用
SELECT c.name, o.product
FROM customer c, order o
WHERE o.value = 150
或
SELECT c.name, o.product
FROM customer c CROSS JOIN order o
WHERE o.value = 150
the two bits of SQL below get the same result
SELECT c.name, o.product
FROM customer c, order o
WHERE c.id = o.cust_id
AND o.value = 150
SELECT c.name, o.product
FROM customer c
INNER JOIN order o on c.id = o.cust_id
WHERE o.value = 150
I've seen both styles used as standard at different companies. From what I've seen, the 2nd one is what most people recommend online. Is there any real reason for this other than style? Does using an Inner Join sometimes have better performance?
I've noticed Ingres and Oracle developers tend to use the first style, whereas Microsoft SQL Server users have tended to use the second, but that might just be a coincidence.
Thanks for any insight, I've wondered about this for a while.
Edit: I've changed the title from 'SQL Inner Join versus Cartesian Product' as I was using the incorrect terminlogy. Thanks for all the responses so far.
Both queries are an inner joins and equivalent. The first is the older method of doing things, whereas the use of the JOIN syntax only became common after the introduction of the SQL-92 standard (I believe it's in the older definitions, just wasn't particularly widely used before then).
The use of the JOIN syntax is strongly preferred as it separates the join logic from the filtering logic in the WHERE clause. Whilst the JOIN syntax is really syntactic sugar for inner joins it's strength lies with outer joins where the old * syntax can produce situations where it is impossible to unambiguously describe the join and the interpretation is implementation-dependent. The [LEFT | RIGHT] JOIN syntax avoids these pitfalls, and hence for consistency the use of the JOIN clause is preferable in all circumstances.
Note that neither of these two examples are Cartesian products. For that you'd use either
SELECT c.name, o.product
FROM customer c, order o
WHERE o.value = 150
or
SELECT c.name, o.product
FROM customer c CROSS JOIN order o
WHERE o.value = 150
这篇关于SQL INNER JOIN语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！