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.
Rows of the outer table that do not meet the condition specified in the On clause in the join are extended with null values for subordinate columns (columns of the subordinate table), whereas the Where clause filters the rows that actually were returned to the final output.
To use the WHERE clause to perform the same join as you perform using the INNER JOIN syntax, enter both the join condition and the additional selection condition in the WHERE clause. The tables to be joined are listed in the FROM clause, separated by commas. This query returns the same output as the previous example.
The purpose of the ON clause is to specify the join conditions, that is, to define how the tables should be joined. Specifically, you define how the records should be matched.
The ON
clause defines the relationship between the tables.
The WHERE
clause describes which rows you are interested in.
Many times you can swap them and still get the same result, however this is not always the case with a left outer join.
- If the
ON
clause fails you still get a row with columns from the left table but with nulls in the columns from the right table.- If the
WHERE
clause fails you won't get that row at all.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With