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 从句。其中包含的列必须是所有表都有的列

a LEFT JOIN b USING (c1, c2, c3)

• 7、右连接工作模式和 左连接 相似。建议使用 左连接。

https://dev.mysql.com/doc/refman/8.0/en/select.html
https://dev.mysql.com/doc/refman/8.0/en/join.html

https://dev.mysql.com/doc/refman/8.0/en/nested-loop-joins.html =》 Nested-Loop Join Algorithms
https://dev.mysql.com/doc/refman/8.0/en/nested-join-optimization.html
https://dev.mysql.com/doc/refman/8.0/en/outer-join-optimization.html