3.2.6 JOIN Syntax:

本节主要描述 SELECT 语句和多表的DELETE 以及 UPDATE语句中的table_references部分里面的JOIN 语法

3.2.6.1 Syntax

table_references:
    table_reference [, table_reference] ...


table_reference: {
    table_factor
    | joined_table
}

table_factor: {
    tbl_name [[AS] alias] 
       | table_subquery [AS] alias 
       | ( 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] ...

注意：这里table_subquery的问题，会在后续章节单独展开讨论

3.2.6.2 与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] ...