在 SQL/MySQL 中，join 语句中的“ON”和“WHERE”有什么区别？

In SQL / MySQL, what is the difference between "ON" and "WHERE" in a join statement?

提问by nonopolarity

The following statements give the same result (one is using on, and the other using where):

以下语句给出了相同的结果（一个是 using on，另一个是 using where）：

mysql> select * from gifts INNER JOIN sentGifts ON gifts.giftID = sentGifts.giftID;
mysql> select * from gifts INNER JOIN sentGifts WHERE gifts.giftID = sentGifts.giftID;

I can only see in a case of a Left Outer Join finding the "unmatched" cases:
(to find out the gifts that were never sent by anybody)

我只能在 Left Outer Join 的情况下看到“无与伦比”的情况：（
找出从未有人送过的礼物）

mysql> select name from gifts LEFT OUTER JOIN sentgifts 
           ON gifts.giftID = sentgifts.giftID 
           WHERE sentgifts.giftID IS NULL;

In this case, it is first using on, and then where. Does the onfirst do the matching, and then wheredoes the "secondary" filtering? Or is there a more general rule of using onversus where? Thanks.

在这种情况下，它首先使用on，然后使用where。是否on首先进行匹配，然后where进行“二级”过滤？或者是否有使用onvs的更一般规则where？谢谢。

回答by Mark Byers

The ONclause defines the relationship between the tables.

该ON子句定义了表之间的关系。

The WHEREclause describes which rows you are interested in.

该WHERE子句描述了您对哪些行感兴趣。

Many times you can swap them and still get the same result, however this is not always the case with a left outer join.

很多时候，您可以交换它们并仍然得到相同的结果，但是对于左外连接，情况并非总是如此。

• If the ONclause fails you still get a row with columns from the left table but with nulls in the columns from the right table.
• If the WHEREclause fails you won't get that row at all.

• 如果ON子句失败，您仍然会得到一行包含左表中的列，但右表中的列中包含空值。
• 如果WHERE子句失败，您根本不会得到该行。

回答by Quassnoi

WHEREis a part of the SELECTquery as a whole, ONis a part of each individual join.

WHERE是SELECT整个查询ON的一部分，是每个单独连接的一部分。

ONcan only refer to the fields of previously used tables.

ON只能引用以前使用过的表的字段。

When there is no actual match against a record in the left table, LEFT JOINreturns one record from the right table with all fields set to NULLS. WHEREclause then evaluates and filter this.

当与左表中的记录没有实际匹配时，从右表LEFT JOIN返回一条记录，所有字段都设置为NULLS。WHERE子句然后评估和过滤这个。

In your query, only the records from giftswithout match in 'sentgifts' are returned.

在您的查询中，仅gifts返回“sentgifts”中不匹配的记录。

Here's the example

这是例子

gifts

1   Teddy bear
2   Flowers

sentgifts

1   Alice
1   Bob

---
SELECT  *
FROM    gifts g
LEFT JOIN
        sentgifts sg
ON      g.giftID = sg.giftID

---

1  Teddy bear   1     Alice
1  Teddy bear   1     Bob
2  Flowers      NULL  NULL    -- no match in sentgifts

---
SELECT  *
FROM    gifts g
LEFT JOIN
        sentgifts sg
ON      g.giftID = sg.giftID
WHERE   sg.giftID IS NULL

---

2  Flowers      NULL  NULL    -- no match in sentgifts

As you can see, no actual match can leave a NULLin sentgifts.id, so only the gifts that had not ever been sent are returned.

如您所见，没有实际匹配可以留下NULLin sentgifts.id，因此只会返回从未发送过的礼物。

回答by RedFilter

When using INNER JOIN, ONand WHEREwill have the same result. So,

使用INNER JOIN, ONand 时WHERE会产生相同的结果。所以，

select *
from Table1 t1
inner join Table2 t2 on t1.id = t2.id
where t1.Name = 'John'

will have the exact same output as

将具有与完全相同的输出

select *
from Table1 t1
inner join Table2 t2 on t1.id = t2.id
    and t1.Name = 'John'

As you have noted, this is not the case when using OUTER JOIN. What query plan gets built is dependent on the database platform as well as query specifics, and is subject to change, so making decisions on that basis alone is not going to give a guaranteed query plan.

正如您所指出的，使用OUTER JOIN. 构建什么查询计划取决于数据库平台以及查询细节，并且可能会发生变化，因此仅在此基础上做出决定并不能保证查询计划。

As a rule of thumb, you should use columns that join your tables in ONclauses and columns that are used for filtering in WHEREclauses. This provides the best readability.

根据经验，您应该使用在ON子句中连接表的列和用于在WHERE子句中过滤的列。这提供了最佳的可读性。

回答by Kangkan

Though the results are same, the 'ON' make the join first and then retrieve the data of the joined set. The retrieval is faster and load is less. But using 'WHERE' cause the two result sets to be fetched first and then apply the condition. So you know what is preferred.

虽然结果相同，但 'ON' 先进行连接，然后检索连接集的数据。检索速度更快，负载更小。但是使用“WHERE”会导致首先获取两个结果集，然后应用条件。所以你知道什么是首选。

回答by Hou

• ON is applied to the set used for creating the permutations of each record as a part ofthe JOIN operation
• WHERE specifies the filter applied afterthe JOIN operation

• ON 应用于用于创建每个记录的排列的集合，作为JOIN 操作的一部分
• WHERE 指定在 JOIN 操作后应用的过滤器

---

---

In effect, ON replaces each field that does not satisfy its condition with a NULL. Given the example by @Quassnoi

实际上，ON 将每个不满足其条件的字段替换为 NULL。鉴于@Quassnoi的例子

gifts

1   Teddy bear
2   Flowers

sentgifts

1   Alice
1   Bob

---
SELECT  *
FROM    gifts g
LEFT JOIN
        sentgifts sg
ON      g.giftID = sg.giftID

---

The LEFT JOIN permutations would have been calculated for the following collections if there was no ON condition:

如果没有 ON 条件，将为以下集合计算 LEFT JOIN 排列：

{ 'Teddy bear': {'ALICE', 'Bob'}, 'Flowers': {'ALICE', 'Bob'} }

{ 'Teddy bear': {'ALICE', 'Bob'}, 'Flowers': {'ALICE', 'Bob'} }

with the g.giftID = sg.giftIDON condition, this is the collections that will be used for creating the permutations:

在g.giftID = sg.giftIDON 条件下，这是将用于创建排列的集合：

{ 'Teddy bear': {'ALICE', 'Bob'}, 'Flowers': {NULL, NULL} }

{ 'Teddy bear': {'ALICE', 'Bob'}, 'Flowers': {NULL, NULL} }

which in effect is:

这实际上是：

{ 'Teddy bear': {'ALICE', 'Bob'}, 'Flowers': {NULL} }

{ 'Teddy bear': {'ALICE', 'Bob'}, 'Flowers': {NULL} }

and so results in the LEFT JOIN of:

所以结果是 LEFT JOIN ：

Teddy bear Alice
Teddy bear Bob
Flowers    NULL

and for a FULL OUTER JOIN you would have:

对于完全外部联接，您将拥有：

{ 'Teddy bear': {'ALICE', 'Bob'}, 'Flowers': {NULL} }for LEFT JOIN and { 'ALICE': {'Teddy bear', NULL}, 'Flowers': {'Teddy bear', NULL} }for RIGHT JOIN:

{ 'Teddy bear': {'ALICE', 'Bob'}, 'Flowers': {NULL} }对于左连接和{ 'ALICE': {'Teddy bear', NULL}, 'Flowers': {'Teddy bear', NULL} }右连接：

Teddy bear Alice
Teddy bear Bob
Flowers    NULL

---

---

If you also had a condition such as ON g.giftID = 1it would be

如果你也有一个条件，例如ON g.giftID = 1它会

{ NULL: {'ALICE', 'Bob'}, 'Flowers': {NULL} }

{ NULL: {'ALICE', 'Bob'}, 'Flowers': {NULL} }

which for LEFT JOIN would result in

对于 LEFT JOIN 将导致

Flowers NULL

Flowers NULL

and for a FULL OUTER JOIN would result in { NULL: {'ALICE', 'Bob'}, 'Flowers': {NULL} }for LEFT JOIN and { 'ALICE': {NULL, NULL}, 'Flowers': {NULL, NULL} }for RIGHT JOIN

对于 FULL OUTER JOIN 将导致 { NULL: {'ALICE', 'Bob'}, 'Flowers': {NULL} }LEFT JOIN 和{ 'ALICE': {NULL, NULL}, 'Flowers': {NULL, NULL} }RIGHT JOIN

NULL    Alice
NULL    Bob
Flowers NULL

---

---

NoteMySQL does not have a FULL OUTER JOIN and you need to apply UNION to LEFT JOIN and RIGHT JOIN

注意MySQL 没有 FULL OUTER JOIN，您需要将 UNION 应用于 LEFT JOIN 和 RIGHT JOIN

回答by brydgesk

If you're using a JOIN, you need to specify the conditions you are joining on. That list goes in an ON clause. A WHERE clause is used to condition data for anywhere in the query.

如果您使用 JOIN，则需要指定加入的条件。该列表包含在 ON 子句中。WHERE 子句用于为查询中的任何位置调节数据。