MYSQL select 语法（三）： JOIN 连接查询

JOIN 从句可以在 SELECT 查询，多表删除，以及更新语句中使用。但更多的是在查询中使用。

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

1、连接语句分为：内连接（inner join）和外连接（outer join）。其中外连接又分为：左外连接（left outer join），右外连接（right outer join）。
JOIN 连接语句的作用简单的讲是：通过某些方式将多个表横向拼接在一块，各个列组成新的行。与之相对的是 UNION 联合语句是：将多个表的行竖向拼接在一起，数据行的堆叠。

如：

T1    T2
----  ----
a  b  a c
1 x   2 z
2 y   3 w


>SELECT * FROM T1 INNER JOIN T2;

a  b  a c
1  x  2  z
1  x  3  w
2  y  2  z
a  y  3  w

>SELECT * FROM T1 LEFT JOIN T2 ON T1.a = T2.a;

a  b  a c
1  x  NULL NULL
2  y  2 z

2、JOIN 连接的原理是使用嵌套循环算法：多个表形成多重循环，根据ON 后面的条件，每一行拼接起来。
以上面的例子为例：
1）内连接：T1表为外循环，T2表是内循环，最后形成笛卡尔积表。最后的总行数是每个表行数的乘积。
执行的过程是：T1表的第一行，和 T2 表的每一行都拼接形成新的一行；T1表的第二行，和 T2 表的每一行都拼接形成新的一行；.... 直至T1表的最后一行和 T2 表的每一行拼接结束。

foreach row t1 in T1 {
   foreach row t2 in T2 {
      t:= t1 || t2
   }
}
output t

2）左连接：T1表为外循环，T2表是内循环
执行的过程是：将T1表的第一行循环比较 T2 的每一行，如果满足连接条件，就拼接形成新的一行，如果T2表没有一行满足条件，则仍然会添加新的一行，这行数据包含 t1，t2的所有列， t1 的值不变，t2 的所有列的值为NULL；
将T1表的第二行循环比较 T2 的每一行，如果满足连接条件，就拼接形成新的一行；
...
以此类推，直至 T1表的最后一行行循环比较 T2 的每一行。

foreach row t1 in T1 {
   t_tmp = NULL
   foreach row t2 in T2 {
      if t1.a = t2.a  
        t_tmp := t1 || t2
   }
   if  t_tmp = NULL 
      t := t1 || NULL
}
output t

3、在 MySQL 中关键字 JOIN, CROSS JOIN, 和 INNER JOIN 在语法上是等同的，都表示内连接。
INNER JOIN 和逗号（,）连接，在没有连接条件时是一样的，都会产生笛卡尔积。但逗号连接的优先级比其他连接都低。
如果存在连接条件时，混用逗号连接和其他连接会出错。
4、search_condition ON 后面的条件，可以是任何用在 where 条件中的表达式。但连接条件是指定怎么连接表，而 WHERE 从句是限制那些行可以导出到结果集。
5、在 LEFT JOIN 中，如果 ON 或 USING 右边的表没有匹配的行，则将这表的一行所有列都设置为 NULL
6、USING(join_column_list) 指定特殊的连接条件，等同于 ON 从句。其中包含的列必须是所有表都有的列