在SQL/MySQL中，"ON"和"ON"之间有什么区别?和"WHERE"在join语句中? [英] In SQL / MySQL, what is the difference between "ON" and "WHERE" in a join statement?

问题描述

以下语句给出相同的结果(一个使用on，另一个使用where):

The following statements give the same result (one is using on, and the other 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)

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

在这种情况下，它首先使用on，然后使用where. on是否先进行匹配，然后where做二次"过滤?还是使用on和where的更通用的规则?谢谢.

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.

推荐答案

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

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

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

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

如果与左表中的记录没有实际匹配，则LEFT JOIN从右表中返回一条记录，且所有字段均设置为NULLS.然后WHERE子句对此进行评估和过滤.

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.

在您的查询中，仅返回gifts中"sentgifts"中不匹配的记录.

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

这是例子

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

如您所见，没有任何实际的匹配可以在sentgifts.id中留下NULL，因此只返回从未发送过的礼物.

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.

这篇关于在SQL/MySQL中，"ON"和"ON"之间有什么区别?和"WHERE"在join语句中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！