在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屋!