MySQL "ON" condition - What does it do?

Question

First of all let me explain that I have searched for at least an hour for anything regarding this MUCH used keyword in MySQL. The problem is that any search engine I use to find information on it matches the word "ON" in the most trivial and unrelated results. I also have had no luck in browsing MySQL's documentation.

I see on used with INNER JOIN as a condition, but I have no idea what it does. An example of usage is

SELECT t1.name, t2.salary
FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;

I know the differences between "WHERE" and "HAVING" is the difference between filtering rows and filtering groups respectively.

I can't imagine what "ON" filters however.

I figured it might be required for filtering when INNER JOIN was used, but I have used WHERE in INNER JOIN cases before, like here:

SELECT g.id, g.name, g.date_created, g.date_updated, g.created_by, 
    c.fullname AS creator_name, g.updated_by, u.fullname AS updater_name, 
    COUNT(i.id) as image_count

FROM gallery_groups g INNER JOIN
    users c INNER JOIN
    users u INNER JOIN
    gallery_images i

WHERE g.created_by=c.id AND g.updated_by=u.id AND i.group=g.id
GROUP BY g.name
ORDER BY g.date_updated DESC, g.name

Any information and/or examples would be appreciated!

Answer 1

INNER JOIN can indeed be written without ON just by moving all the ON clauses to the where clause. With a sane optimizer, it won't make a difference performance-wise (but it'll still make the query clearer!).

Outer joins (e.g., LEFT JOIN) can not be, because of null handling.

SELECT a.foo, b.bar FROM a LEFT JOIN b ON (a.fk = b.pk)

will return b.bar=NULL if a.fk does not exist in b.

SELECT a.foo, b.bar FROM a, b WHERE a.fk.b.pk

will not return a row if a.fk does not exist in b.

FYI: Join syntax is http://dev.mysql.com/doc/refman/5.0/en/join.html