在SQL/MySQL中，“ON"和“ON"有什么区别?和“哪里"在连接语句中? [英] 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"有什么区别?和“哪里"在连接语句中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！