Re: Join Syntax Confusion

[Rick James]

Migrating a condition from the ON clause to the WHERE clause always(?) has no effect in a plain JOIN. But it may make a difference in the resultset for a LEFT JOIN. EXTENDED EXPLAIN + SHOW WARNINGS is handy for seeing when the optimizer does such things. (There are also rewrites involving other things, such as DISTINCT and NOT EXISTS.)

Also, all (nearly all) JOINs are done via "Nested Loop Join". Hence, there is no option for an efficiency difference between ON and WHERE.

Probably the optimizer simplifies its life by
1. Migrating any ONs to WHERE that are correct to do so.
2. Deciding what order to do the ANDed clauses in the WHERE. Note: No need in this step to be confused by ON vs WHERE.

Another note... "Comma JOIN" (FROM a,b) is the same as JOIN (FROM a JOIN b). Almost. The precedence actually changed a few years ago (do be ANSI-compatible). That is, the first of these is the same as one of the other two:
FROM a, b JOIN c
FROM (a, b) JOIN c
FROM a, (b JOIN c)
Mixing comma-join and JOIN should be avoided. Parentheses are always welcome.