The bit your missing is the awkwardness of expressing outer joins. The way Oracle does it is an existence proof of its awkwardness.
The 'FROM' clause defaults to Cartesian product for multiple tables. You can then specify your join conditions in the where clause, but it makes outer joins harder to write - Oracle does them with special (+) operator, but it's clumsy.
It doesn't (shouldn't) make much difference at all to the database whether you specify your join condition after the `ON` or in the `WHERE` - if it doesn't hoist `WHERE` conditions into the join clause, and potentially change the join order because of it, it's missing big optimization opportunities.
Ah... that makes sense. I have experimented a bit since my original comment and so far have not been able to come up with a syntactically elegant way of implementing LEFT JOIN in an imaginary MySQL that had inner JOIN as the only JOIN. This actually makes me wonder... if SQL only had inner JOIN, what would be the syntactically most elegant way of implementing the other JOINs? The various replies in this comment tree make me suppose that the answer would depend on the precise flavor of SQL being used.
The 'FROM' clause defaults to Cartesian product for multiple tables. You can then specify your join conditions in the where clause, but it makes outer joins harder to write - Oracle does them with special (+) operator, but it's clumsy.
It doesn't (shouldn't) make much difference at all to the database whether you specify your join condition after the `ON` or in the `WHERE` - if it doesn't hoist `WHERE` conditions into the join clause, and potentially change the join order because of it, it's missing big optimization opportunities.