JOIN clause

Definition:

• Breaking data into multiple tables enables more efficient storage, easier manipulation, and greater scalability
• mechanism used to associate, or join, tables within a SELECT statement.
• You can join multiple tables so that a single set of output is returned and the join associates the correct rows in each table on the fly.
• A table reference is also known as a join expression.
• JOIN clause returns a table referrence

Synopis:

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