MySQL "ON" condition - What does it do?

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 unrela开发者_高级运维ted 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!

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

Instead writing long WHERE statement you put dependency between tables in JOIN closure after ON key word

It speed up searching process in database ( it realted to kartezjan matrix ), make your code cleaner, and easier to put real WHERE conditions

FYI, both are identical except INNER JOIN ... ON is ANSI 92 syntax

You probably need to google ansi 92 syntax vs ansi 86/89 syntax

• http://www.rampant-books.com/t_super_sql_131_joins_ansi_syntax.htm
• http://www.learnitfirst.com/Course/150/Video/545/ANSI-89-or-ANSI-92-JOIN-Syntax.aspx