MySQL的JOIN总结

JOIN对于table_references部分 SELECT语句以及多表DELETE和 UPDATE语句， MySQL支持以下语法：

table_references:
    escaped_table_reference [, escaped_table_reference] ...

escaped_table_reference: {
    table_reference
  | { OJ table_reference }
}

table_reference: {
    table_factor
  | joined_table
}

table_factor: {
    tbl_name [PARTITION (partition_names)]
        [[AS] alias] [index_hint_list]
  | [LATERAL] table_subquery [AS] alias [(col_list)]
  | ( table_references )
}

joined_table: {
    table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification]
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
  | table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor
}

join_specification: {
    ON search_condition
  | USING (join_column_list)
}

join_column_list:
    column_name [, column_name] ...

index_hint_list:
    index_hint [, index_hint] ...

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

index_list:
    index_name [, index_name] ...

编写JOIN语句的常见问题

• 在MySQL中JOIN, INNER JOIN, CROSS JOIN是等效的.
  以下三条语句在MySQL中是等价的.

SELECT * FROM sys_role as t1 JOIN sys_user_role AS t2 ON t1.id = t2.role_id; 
SELECT * FROM sys_role as t1 INNER JOIN sys_user_role AS t2 ON t1.id = t2.role_id; 
SELECT * FROM sys_role as t1 CROSS JOIN sys_user_role AS t2 ON t1.id = t2.role_id; 

• 表引用可以使用tbl_name as alias_name或者tbl_name alias_name来设置别名.

SELECT * FROM img_image as t1 join sys_role t2 on t1.id=t2.id;