在SQL / MySQL中，连接语句中的“ON”和“WHERE”有什么区别?

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

下面的语句给出了相同的结果(一个是on，另一个是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)

我只能在一个左外加入的情况下找到“不匹配”的情况:(找出从来没有人送过的礼物)

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 on first do the matching, and then where does the "secondary" filtering? Or is there a more general rule of using on versus where? Thanks.

在这种情况下，首先是on，然后是where。on首先进行匹配吗，然后“次要”过滤在哪里?还是说on和where有更一般的用法?谢谢。

6 个解决方案

#1

WHERE is a part of the SELECT query as a whole, ON is a part of each individual join.

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

ON can only refer to the fields of previously used tables.

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

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

当在左边的表中没有实际的匹配时，左JOIN将从右表返回一个记录，所有字段都设置为null。WHERE子句然后对其进行评估和筛选。

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

在您的查询中，只有“送出”中没有匹配的礼物记录被退回。

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 NULL in sentgifts.id, so only the gifts that had not ever been sent are returned.

正如你所看到的，没有任何一场比赛可以在句子中留下零。id，所以只有没有发送的礼物才会被退回。

#2

The ON clause defines the relationship between the tables.

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

The WHERE clause 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 ON clause fails you still get a row with columns from the left table but with nulls in the columns from the right table.
如果ON子句失败，您仍然可以从左侧表中获得具有列的行，但从右侧表中获得列中的null。
If the WHERE clause fails you won't get that row at all.
如果WHERE子句失败，则根本无法得到该行。

#3

When using INNER JOIN, ON and WHERE will have the same result. So,

当使用内连接时，ON和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.

正如您所注意到的，在使用外部连接时并非如此。所构建的查询计划依赖于数据库平台和查询细节，并且可能会发生更改，因此仅在此基础上进行决策不会提供一个有保证的查询计划。

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

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

#4

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”可以使两个结果集首先被获取，然后应用条件。所以你知道什么是首选。

#5

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.

如果您正在使用连接，您需要指定您正在连接的条件。那份清单包含在ON子句中。WHERE子句用于为查询中的任何地方设置数据条件。

#6

ON is applied to the set used for creating the permutations of each record as a part of the JOIN operation
ON应用于作为连接操作的一部分创建每个记录的排列的集合
WHERE specifies the filter applied after the JOIN operation
在哪里指定在联接操作之后应用的筛选器

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

实际上，用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:

如果没有条件，则为以下集合计算左连接排列:

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

{“泰迪熊”:{“爱丽丝”,“鲍勃”},“花”:{“爱丽丝”,“鲍勃”} }

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

与g。giftID = sg。giftID条件下，这是用于创建排列的集合:

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

{“泰迪熊”:{“爱丽丝”,“鲍勃”},“花”:{零,零} }

which in effect is:

这实际上是:

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

{“泰迪熊”:{“爱丽丝”,“鲍勃”},“花”:{零} }

and so results in the LEFT JOIN of:

因此，左连接为:

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 = 1 it would be

如果你也有一个条件，比如g。giftID = 1

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

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

which for LEFT JOIN would result in

左连接的结果是什么

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

对于完整的外部连接，将会产生{NULL: {'ALICE'， 'Bob'}， 'Flowers': {NULL}表示左连接，{'ALICE': {NULL, NULL}， 'Flowers': {NULL, NULL}表示右连接

NULL    Alice
NULL    Bob
Flowers NULL

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

注意，MySQL没有完整的外部连接，您需要将UNION应用于左连接和右连接。

#1