In SQL / MySQL, what is the difference between "ON" and "WHERE" in a join statement?
join, mysql, sql, where-clause
Solution
`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.
Problem
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.