表连接的约束条件WHERE ON USING
In SQL / MySQL, what is the difference between “ON” and “WHERE” in a join statement?
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.
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.