Search code examples
mysqlleft-joininner-joinright-join

Mysql join practices use LEFT JOIN and INNER JOIN only?

As I am reading about joins I realize that only two of those are in work as others are just the same. Am I correct?

INNER JOIN = JOIN LEFT JOIN = LEFT OUTER JOIN

Are there any joins that you actually use?

Solution

  • From http://dev.mysql.com/doc/refman/5.0/en/join.html:

    join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON conditional_expr
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
  | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor

    Text within the braces is optional. The INNER and CROSS keywords are optional for a normal JOIN (the documentation mentions this). The OUTER keyword is optional for outer (LEFT|RIGHT) joins.

    This could be a bit clearer in that NATURAL LEFT JOIN is not the same as NATURAL JOIN, but the INNER, CROSS, and OUTER keywords are optional when used in the correct spot.

    Note that this is MySQL specific. It is not the case in standard SQL.