mysql on_在SQL / MySQL中，“ON”和“ON”之间的区别是什么？和“何处”在连接声明中？...

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.