(Aside from JD’s Answer, …)
SQL syntax is picky;
WHERE comes after all the
ON clauses should say how the table is ‘related’ to the preceding table. Otherwise, it works identically to
I’ll quibble with the “notoriously inefficient”. This depends on the
WHERE. In particular, if
WHERE filters on more than one table, the Optimizer must guess at what order to look at the tables, and may fail to do the best job. Also, it implies gathering data from some tables for throwing out the rows due to a
WHERE clause that is acted on later.
The Optimizer is free to look at the tables in any order and rearrange
WHERE clauses as long as it leads to the same resultset.
An optimization that shows up some times:
SELECT ... FROM a JOIN b ON b.x = a.y WHERE b.x = 123
The Optimizer is smart enough to realize that
a.y is necessarily filtered to “123”. This might be advantageous, for example, if
WHERE can come
ORDER BY, and
LIMIT, each optional, but necessarily in that order.
HAVING is identical to
HAVING can see aggregates, but
WHERE cannot. Example:
SELECT user_id, COUNT(*) AS ct FROM t GROUP BY user_id HAVING ct >= 2
This provides any users that show up at least twice. This cannot be done with
WHERE. (OK, you could do it less efficiently via a subquery with