在SQL/MySQL中,“ON"和“ON"有什么区别?和“哪里"在连接语句中?
以下语句给出相同的结果(一个使用 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.
相关文章