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;
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 is a part of the SELECT query as a whole, ON is a part of each individual join.
ON can only refer to the fields of previously used tables.
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.
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.