SELECT * FROM table1 CROSS JOIN table2
SELECT * FROM table1 JOIN table2
SELECT * FROM table1,table2

http://dev.mysql.com/doc/refman/5.0/en/join.html
join_table:
table_reference [INNER | CROSS] JOIN table_factor [join_condition]

a. LEFT [OUTER] JOIN
SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.column

b. RIGHT [OUTER] JOIN
SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column=table2.column

SELECT <column_name> FROM <table1> LEFT JOIN <table2> USING (<column_name>)

mysql> SELECT  artists.Artist, cds.title, genres.genre
-> FROM cds
-> LEFT JOIN genres
-> ON cds.genreID = genres.genreID
-> LEFT JOIN artists
-> ON cds.artistID = artists.artistID;

mysql> SELECT artists.Artist, cds.title, genres.genre
-> FROM cds
-> LEFT JOIN genres
-> ON cds.genreID = genres.genreID
-> LEFT JOIN artists
-> ON cds.artistID = artists.artistID
-> WHERE (genres.genre = 'Pop');
--------------------------------------------

[INNER | CROSS] JOIN

table_references:
table_reference [,
table_reference

] ...

//不同的JOIN EXPRESSION之间使用','分割
A table reference is also known as a join expression.

table_reference:
table_factor
| join_table

//每个JOIN EXPRESSION由数据表table_factor以及JOIN表达式构成join_table

table_factor:

tbl_name [[AS] alias] [index_hint)]
| ( table_references )
| { OJ
      table_reference LEFT OUTER JOIN table_reference
ON conditional_expr

}

//数据表table_factor，注意其递归定义的table_references

join_table:
table_reference [INNER | CROSS] JOIN
table_factor [join_condition]
| table_reference STRAIGHT_JOIN
table_factor
| table_reference STRAIGHT_JOIN table_factor ON condition
|
table_reference LEFT [OUTER] JOIN table_referencejoin_condition
|
table_reference NATURAL [LEFT [OUTER]] JOIN table_factor
| table_reference RIGHT [OUTER] JOIN
table_referencejoin_condition
| table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor

//数据表的连接表达式join_table

join_condition:
ON conditional_expr

| USING (column_list

)

//连接表达式的连接条件定义使用ON或者USING

index_hint:
USE {INDEX|KEY} [FOR JOIN] (index_list
)
| IGNORE {INDEX|KEY} [FOR JOIN] (index_list)
| FORCE {INDEX|KEY} [FOR JOIN] (index_list)


index_list:
index_name [, index_name] ...

In MySQL,  CROSS JOIN is a syntactic equivalent to  INNER JOIN (they can replace each other). In standard SQL, they are not equivalent.  INNER JOIN is used with an  ON clause,  CROSS JOIN is used otherwise.

SELECT t1.name, t2.salary
FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;
可以对数据表使用别名

3. ,运算符
例如
SELECT * FROM table1,table2
由于在MySQL中INNER JOIN与CROSS JOIN相同，INNER JOIN和 , 在MySQL也相同，都是产生两个表的笛卡尔积Cartesian Product
(等于两个表格的行数乘积)

但是,号的优先级要低于INNER JOIN, CROSS JOIN, LEFT JOIN

因此
If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur.

4. 什么时候使用ON，什么时候使用WHERE
ON应该用户数据表连接的时候指定连接条件；

WHERE用于用户限制所选取的列

例如ON a.column=b.column
WHERE a.column='hello'

5. 可以使用LEFT JOIN查看，两个连接的表中，不符合连接条件的部分，因为不符合条件的部分LEFT JOIN之后会显示为NULL
If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table:

SELECT left_tbl.*
FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
WHERE right_tbl.id IS NULL;

This example finds all rows in left_tbl with an id value that is not present in right_tbl (that is, all rows in left_tbl with no corresponding row in right_tbl). This assumes that right_tbl.id is declared NOT NULL.

6.
当别连接的表指定连接条件的列举有相同的名称的时候，不需要
ON a.column=b.column不同的时候才使用ON a.column_a=b.column_b
可以使用USING (column)
当然也可以使用多个USING (c1,c2,c3)

The USING(column_list) clause names a list of columns that must exist in both tables. If tables a and b both contain columns c1, c2, and c3, the following join compares corresponding columns from the two tables:

a LEFT JOIN b USING (c1,c2,c3)

7.
其他的：
#
The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.

#
RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it is recommended that you use LEFT JOIN instead of RIGHT JOIN.
#
The { OJ ... LEFT OUTER JOIN ...} syntax shown in the join syntax description exists only for compatibility with ODBC. The curly braces in the syntax should be written literally; they are not metasyntax as used elsewhere in syntax descriptions.

#
STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

http://www.w3schools.com/sql/sql_join.asp
http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php
http://dev.mysql.com/doc/refman/5.0/en/join.html