SQL INNER JOIN 语法

SQL INNER JOIN syntax

提问by Dean Madden

the two bits of SQL below get the same result

下面的两段 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

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.

我注意到 Ingres 和 Oracle 开发人员倾向于使用第一种风格，而 Microsoft SQL Server 用户倾向于使用第二种风格，但这可能只是巧合。

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.

编辑：由于我使用了不正确的术语，我已经将标题从“SQL 内部连接与笛卡尔积”更改了。感谢您到目前为止的所有回复。

采纳答案by Cruachan

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).

这两个查询都是内部联接和等效的。第一种是较旧的做事方法，而 JOIN 语法的使用仅在引入 SQL-92 标准后才变得普遍（我相信它在较旧的定义中，只是在那之前并没有特别广泛使用）。

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.

强烈推荐使用 JOIN 语法，因为它将连接逻辑与 WHERE 子句中的过滤逻辑分开。虽然 JOIN 语法实际上是内连接的语法糖，但它的优势在于外连接，在这种情况下，旧的 * 语法可能会产生无法明确描述连接并且解释依赖于实现的情况。[左 | RIGHT] JOIN 语法避免了这些陷阱，因此为了一致性，在所有情况下都最好使用 JOIN 子句。

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

回答by WW.

To answer part of your question, I think early bugs in the JOIN ... ON syntax in Oracle discouraged Oracle users away from that syntax. I don't think there are any particular problems now.

为了回答您的部分问题，我认为 Oracle 中 JOIN ... ON 语法中的早期错误使 Oracle 用户远离该语法。我不认为现在有任何特别的问题。

They are equivalent and should be parsed into the same internal representation for optimization.

它们是等效的，应该被解析为相同的内部表示以进行优化。

回答by erikkallen

Oracle was late in supporting the JOIN ... ON (ANSI) syntax (not until Oracle 9), that's why Oracle developers often don't use it.

Oracle 较晚才支持 JOIN ... ON (ANSI) 语法（直到 Oracle 9），这就是 Oracle 开发人员通常不使用它的原因。

Personally, I prefer using ANSI syntax when it is logically clear that one table is driving the query and the others are lookup tables. When tables are "equal", I tend to use the cartesian syntax.

就我个人而言，我更喜欢使用 ANSI 语法，当逻辑上很清楚一个表正在驱动查询而其他表是查找表时。当表“相等”时，我倾向于使用笛卡尔语法。

The performance should not differ at all.

性能应该完全不同。

回答by Ed Guiness

Actually these examples are equivalent and neither is a cartesian product. A cartesian product is returned when you join two tables without specifying a join condition, such as in

实际上这些例子是等价的，也不是笛卡尔积。连接两个表而不指定连接条件时，将返回笛卡尔积，例如

select *
from t1,t2

There is a good discussion of this on Wikipedia.

维基百科上对此有很好的讨论。

回答by Ed Guiness

The JOIN... ON... syntax is a more recent addition to ANSI and ISO specs for SQL. The JOIN... ON... syntax is generally preferred because it 1) moves the join criteria out of the WHERE clause making the WHERE clause just for filtering and 2) makes it more obvious if you are creating a dreaded Cartesian product since each JOIN must be accompanied by at least one ON clause. If all the join criteria are just ANDed in the WHERE clause, it's not as obvious when one or more is missing.

JOIN... ON... 语法是对 SQL 的 ANSI 和 ISO 规范的最新补充。JOIN... ON... 语法通常是首选的，因为它 1) 将连接条件移出 WHERE 子句，使 WHERE 子句仅用于过滤和 2) 如果您正在创建一个可怕的笛卡尔积，它会更加明显，因为每个JOIN 必须至少伴随一个 ON 子句。如果所有连接条件都只是在 WHERE 子句中进行了 AND 运算，那么当缺少一个或多个条件时就不那么明显了。

回答by Jim Anderson

Both queries are performing an inner join, just different syntax.

两个查询都在执行内部联接，只是语法不同。

回答by Vlad Mihalcea

This is a very common question, so this answer is based on this articleI wrote.

这是一个很常见的问题，所以这个答案是基于我写的这篇文章。

TL;DR

TL; 博士

An INNER JOIN statement can be rewritten as a CROSS JOIN with a WHERE clause matching the same condition you used in the ON clause of the INNER JOIN query.

INNER JOIN 语句可以重写为 CROSS JOIN，其 WHERE 子句与您在 INNER JOIN 查询的 ON 子句中使用的条件相同。

Table relationship

表关系

Considering we have the following postand post_commenttables:

考虑到我们有以下post和post_comment表格：

The posthas the following records:

将post具有以下记录：

| id | title     |
|----|-----------|
| 1  | Java      |
| 2  | Hibernate |
| 3  | JPA       |

and the post_commenthas the following three rows:

并且post_comment具有以下三行：

| id | review    | post_id |
|----|-----------|---------|
| 1  | Good      | 1       |
| 2  | Excellent | 1       |
| 3  | Awesome   | 2       |

SQL INNER JOIN

SQL 内连接

The SQL JOIN clause allows you to associate rows that belong to different tables. For instance, a CROSS JOINwill create a Cartesian Product containing all possible combinations of rows between the two joining tables.

SQL JOIN 子句允许您关联属于不同表的行。例如，CROSS JOIN将创建一个笛卡尔积，其中包含两个连接表之间所有可能的行组合。

While the CROSS JOIN is useful in certain scenarios, most of the time, you want to join tables based on a specific condition. And, that's where INNER JOIN comes into play.

虽然 CROSS JOIN 在某些情况下很有用，但大多数情况下，您希望根据特定条件连接表。而且，这就是 INNER JOIN 发挥作用的地方。

The SQL INNER JOIN allows us to filter the Cartesian Product of joining two tables based on a condition that is specified via the ON clause.

SQL INNER JOIN 允许我们根据通过 ON 子句指定的条件过滤连接两个表的笛卡尔积。

SQL INNER JOIN - ON "always true" condition

SQL INNER JOIN - 在“始终为真”条件下

If you provide an "always true" condition, the INNER JOIN will not filter the joined records, and the result set will contain the Cartesian Product of the two joining tables.

如果您提供“始终为真”的条件，则 INNER JOIN 不会过滤连接的记录，结果集将包含两个连接表的笛卡尔积。

For instance, if we execute the following SQL INNER JOIN query:

例如，如果我们执行以下 SQL INNER JOIN 查询：

SELECT
   p.id AS "p.id",
   pc.id AS "pc.id"
FROM post p
INNER JOIN post_comment pc ON 1 = 1

We will get all combinations of postand post_commentrecords:

我们将获得post和post_comment记录的所有组合：

| p.id    | pc.id      |
|---------|------------|
| 1       | 1          |
| 1       | 2          |
| 1       | 3          |
| 2       | 1          |
| 2       | 2          |
| 2       | 3          |
| 3       | 1          |
| 3       | 2          |
| 3       | 3          |

So, if the ON clause condition is "always true", the INNER JOIN is simply equivalent to a CROSS JOIN query:

因此，如果 ON 子句条件“始终为真”，则 INNER JOIN 仅等效于 CROSS JOIN 查询：

SELECT
   p.id AS "p.id",
   pc.id AS "pc.id"
FROM post p
CROSS JOIN post_comment
WHERE 1 = 1
ORDER BY p.id, pc.id

SQL INNER JOIN - ON "always false" condition

SQL INNER JOIN - 在“始终为假”条件下

On the other hand, if the ON clause condition is "always false", then all the joined records are going to be filtered out and the result set will be empty.

另一方面，如果 ON 子句条件为“始终为假”，则所有连接的记录都将被过滤掉，结果集将为空。

So, if we execute the following SQL INNER JOIN query:

因此，如果我们执行以下 SQL INNER JOIN 查询：

SELECT
   p.id AS "p.id",
   pc.id AS "pc.id"
FROM post p
INNER JOIN post_comment pc ON 1 = 0
ORDER BY p.id, pc.id

We won't get any result back:

我们不会得到任何结果：

| p.id    | pc.id      |
|---------|------------|

That's because the query above is equivalent to the following CROSS JOIN query:

这是因为上面的查询等效于以下 CROSS JOIN 查询：

SELECT
   p.id AS "p.id",
   pc.id AS "pc.id"
FROM post p
CROSS JOIN post_comment
WHERE 1 = 0
ORDER BY p.id, pc.id

SQL INNER JOIN - ON clause using the Foreign Key and Primary Key columns

SQL INNER JOIN - 使用外键和主键列的 ON 子句

The most common ON clause condition is the one that matches the Foreign Key column in the child table with the Primary Key column in the parent table, as illustrated by the following query:

最常见的 ON 子句条件是将子表中的外键列与父表中的主键列匹配，如以下查询所示：

SELECT
   p.id AS "p.id",
   pc.post_id AS "pc.post_id",
   pc.id AS "pc.id",
   p.title AS "p.title",
   pc.review  AS "pc.review"
FROM post p
INNER JOIN post_comment pc ON pc.post_id = p.id
ORDER BY p.id, pc.id

When executing the above SQL INNER JOIN query, we get the following result set:

当执行上述 SQL INNER JOIN 查询时，我们得到以下结果集：

| p.id    | pc.post_id | pc.id      | p.title    | pc.review |
|---------|------------|------------|------------|-----------|
| 1       | 1          | 1          | Java       | Good      |
| 1       | 1          | 2          | Java       | Excellent |
| 2       | 2          | 3          | Hibernate  | Awesome   |

So, only the records that match the ON clause condition are included in the query result set. In our case, the result set contains all the postalong with their post_commentrecords. The postrows that have no associated post_commentare excluded since they can not satisfy the ON Clause condition.

因此，只有符合 ON 子句条件的记录才会包含在查询结果集中。在我们的例子中，结果集包含所有的post和他们的post_comment记录。post没有关联的行post_comment被排除，因为它们不能满足 ON 子句条件。

Again, the above SQL INNER JOIN query is equivalent to the following CROSS JOIN query:

同样，上面的 SQL INNER JOIN 查询等效于以下 CROSS JOIN 查询：

SELECT
   p.id AS "p.id",
   pc.post_id AS "pc.post_id",
   pc.id AS "pc.id",
   p.title AS "p.title",
   pc.review  AS "pc.review"
FROM post p, post_comment pc
WHERE pc.post_id = p.id

The non-struck rows are the ones that satisfy the WHERE clause, and only these records are going to be included in the result set. That's the best way to visualize how the INNER JOIN clause works.

未命中的行是满足 WHERE 子句的行，并且只有这些记录将包含在结果集中。这是可视化 INNER JOIN 子句如何工作的最佳方式。

| p.id | pc.post_id | pc.id | p.title   | pc.review |
|------|------------|-------|-----------|-----------|
| 1    | 1          | 1     | Java      | Good      |
| 1    | 1          | 2     | Java      | Excellent |
| 1    | 2          | 3     | Java      | Awesome   |
| 2    | 1          | 1     | Hibernate | Good      |
| 2    | 1          | 2     | Hibernate | Excellent |
| 2    | 2          | 3     | Hibernate | Awesome   |
| 3    | 1          | 1     | JPA       | Good      |
| 3    | 1          | 2     | JPA       | Excellent |
| 3    | 2          | 3     | JPA       | Awesome   |

Not that this only applies to INNER JOIN, not for OUTER JOIN.

并不是这仅适用于 INNER JOIN，不适用于 OUTER JOIN。