翻譯完成有一種莫名的幸福感---

JOIN 語法

Mysql支持select 語句和多表delete、update 語句的表引用部分的下述 JOIN 語法插掂。

表引用也稱為連接表達(dá)式。
表引用可能包含一個 PARTITION 選項算色，包括一系列的逗號分隔區(qū)間魁瞪、子區(qū)間，或者兩者皆有床佳。這個選項遵循表命名，位于任何別名聲明之前榄审。此選項的作用是選擇的行只能來自于列出的區(qū)間或者子區(qū)間砌们。任何未列舉出來的區(qū)間或子區(qū)間都被忽略。想了解更多信息和例子搁进，看章節(jié)23.5浪感，“Partition Selection”

與標(biāo)準(zhǔn)的 SQL 相比，table_factor 的語法是 MySQL的擴(kuò)展饼问。標(biāo)準(zhǔn) SQL 只接受 table_reference,不接受一對圓括號內(nèi)的他們的列表影兽。

如果表引用元素中的每個逗號都被當(dāng)做等價于 inner join，那這是一個保守的擴(kuò)展莱革。如：

select * from t1 left join (t2,t3,t4) on (t2.a=t1.a and t3.b=t1.b and t4.c=t1.c)

等價于

select * from t1 left join (t2 cross join t3 cross join t4) on (t2.a=t1.a and t3.b=t1.b and t4.c=t1.c)

在 mysql峻堰，join,cross join,inner join 語義上是相同的(他們可以互相替代)。在標(biāo)準(zhǔn)的 SQL驮吱，他們不一樣茧妒。inner join 與 on 子句一起使用，否則使用交叉連接左冬。

總的來說桐筏，在連接表達(dá)式中包含 inner join操作時，括號可以被忽略拇砰。MySQL 也支持嵌套的連接梅忌。參照章節(jié)8.2.1.7,”Nested Join Optimization”狰腌。

索引提示可以具體影響MySQL優(yōu)化器如何使用索引。更多的信息牧氮，參照章節(jié)8.9.4琼腔，“Index Hints”.優(yōu)化器提示和 optimizer_switch 系統(tǒng)變量是影響優(yōu)化器使用索引的方式，參照章節(jié)8.9.2踱葛，“Optimizer Hints”和章節(jié)8.9.3丹莲，“Switchable Optimizations”.

下面列舉一些當(dāng)寫 joins 語句時需要納入考慮的因素：

1. 表引用可以使用別名，通過tb1_name as alias_name或 tb1_name alias_name 語法表示尸诽。
2. table_subquery 也可以認(rèn)為是位于 from 語句后的衍生表或子查詢甥材。參照章節(jié)13.2.11.8，“Derived Tables”性含。像這樣的子查詢必須添加別名洲赵，以便于為子查詢結(jié)果提供一個表名，并可以選擇性的在括號內(nèi)包含表的列名商蕴。下面是一個小例子：

select * from (select 1,2,3) as t1;

3. inner join和逗號在沒有 join 條件的情況下語義是一樣的：兩者都在指定的表之間產(chǎn)生笛卡爾積（就是叠萍，第一張表的每一行與第二張表的每一行連接）。
   可是绪商，逗號操作符的優(yōu)先級比 inner join苛谷，cross join，left join等低部宿。如果你將逗號連接與其他類型連接混合抄腔，會產(chǎn)生on語句后未知列“col_name”的錯誤。處理這個問題的方法將在本章節(jié)后面闡述理张。
4. 通過 on 使用的condition_expr可以是任何可以在 where 子句中使用的條件表達(dá)式∶嗷迹總的來說雾叭，on子句服務(wù)于具體指定如何連接表的條件，而 where 子句限制哪些行包括在結(jié)果集中落蝙。
5. 在left join中使用 on 或 using 時织狐，如果右表無任何匹配的行，那么右表所有的行都會設(shè)為 null筏勒。你可以使用這個用法來查找某個表在其他表無副本的行移迫。

select left_tb1.* from left_tb1 left join right_tb1 on left_tb1.id=right_tb1.id where right_tb1.id is null;

這個例子查找 left_tb1的 id 值并沒有出現(xiàn)在 right_tb1的所有行（也就是，left_tb1與 right_tb1無關(guān)的所有行）管行。參照章節(jié)8.2.1.8厨埋，“Outer join Optimization”。

6. using(column_list)子句表示必須同時出現(xiàn)在兩張表中的一些列名捐顷。如果表 a 和表 b都包含列 c1,c2和 c3荡陷，下述連接比較兩張表相關(guān)聯(lián)的列雨效。

a left join b using(c1,c2,c3)

7. natural [left] join連接兩張表定義為語義上等同于 inner join 或者 具有 use 子句的left join，use 子句命名兩個表中都存在的列废赞。
8. Right join作用與 left join 相似徽龟。為了讓代碼可以便攜跨越數(shù)據(jù)庫，推薦使用 left join 代替 right join唉地。
9. 出現(xiàn)在 join 語法描述中的{OJ…}語法只在 ODBC 中兼容据悔。語法中的大括號應(yīng)該按照字面意思寫；他們不是在各個地方都能使用的語法描述的元語法耘沼。
   你可以在{OJ …}中使用其他類型的連接屠尊，如 inner join，right join耕拷，right outer join讼昆。這有助于兼容不是官方的ODBC(Open Database Connectivity,開放數(shù)據(jù)庫互聯(lián)，提供一種標(biāo)準(zhǔn)的 API 方法來訪問數(shù)據(jù)庫管理系統(tǒng))語法的第三方應(yīng)用骚烧。
10. straight_join 與 join 相似浸赫，左表總是比右表優(yōu)先讀取。這可以使用在連接優(yōu)化器以次優(yōu)順序處理表的少數(shù)情況下赃绊。

SELECT * FROM table1, table2;

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

SELECT * FROM table1 LEFT JOIN table2 USING (id);

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
  LEFT JOIN table3 ON table2.id = table3.id;

自然連接和使用的 using 的連接既峡，包括外連接的變體，處理都遵照 SQL:2003標(biāo)準(zhǔn)碧查。

11. 在自然連接中多余的列不會出現(xiàn)运敢。思考下述語句：

SELECT * FROM table1, table2;

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

SELECT * FROM table1 LEFT JOIN table2 USING (id);

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
  LEFT JOIN table3 ON table2.id = table3.id;

在第一個 select 語句中，列 j 出現(xiàn)在兩張表中所以成為了 join 的列忠售，所以传惠，根據(jù)標(biāo)準(zhǔn) SQL，它只會在結(jié)果集中出現(xiàn)一次稻扬，而不是兩次卦方。同樣的，在第二個 select 語句中泰佳， j列在 using 子句中命名盼砍，也只會在結(jié)果集中出現(xiàn)一次。
冗余的列消除和列順序的出現(xiàn)遵循 SQL 規(guī)范逝她，產(chǎn)生了這樣的展示順序浇坐。
A. 首先，兩張連接表的合并列的順序黔宛，由第一張表的出現(xiàn)順序決定近刘。
B. 第二，第一張表獨有的列，以第一張表出現(xiàn)的順序決定跌宛。
C.第三酗宋，第二張表獨有的列，以第二章表出現(xiàn)的順序決定疆拘。
代替兩個公共列的單個結(jié)果列被定義為合并操作蜕猫。就是，對于兩列t1.a 和t2.a 結(jié)果為單個連接列 a被定義為 a=coalesce(t1.a,t2.a),其中：

COALESCE(x, y) = (CASE WHEN x IS NOT NULL THEN x ELSE y END)

如果連接操作是任何其他連接哎迄，連接的結(jié)果列由連接表的所有列串聯(lián)組成回右。

由定義的和并列引起的后果是，對于外連接漱挚，如果兩者之一的列總是 null,合并的列包含非空的列翔烁。如果兩個都不為空，兩個公共列有相同的值旨涝，所以無需選擇哪個值作為合并列的值蹬屹。一個簡單的方式解釋是考慮外連接的合并列代表內(nèi)部表連接的公共列。假設(shè)表t1(a,b)和t2(a,c)有如下內(nèi)容：
t1 t2
1 x 2 z
2 y 3 w
對于這個連接白华，列 a 包含t1.a的值：

mysql> SELECT * FROM t1 NATURAL LEFT JOIN t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | x    | NULL |
|    2 | y    | z    |
+------+------+------+

作為對比慨默，這個連接，列 a 包含 t2.a的值：

mysql> SELECT * FROM t1 NATURAL RIGHT JOIN t2;
+------+------+------+
| a    | c    | b    |
+------+------+------+
|    2 | z    | y    |
|    3 | w    | NULL |
+------+------+------+

將這些結(jié)果與其他使用 join … on 的等價查詢比較

mysql> SELECT * FROM t1 LEFT JOIN t2 ON (t1.a = t2.a);
+------+------+------+------+
| a    | b    | a    | c    |
+------+------+------+------+
|    1 | x    | NULL | NULL |
|    2 | y    |    2 | z    |
+------+------+------+------+

mysql> SELECT * FROM t1 RIGHT JOIN t2 ON (t1.a = t2.a);
+------+------+------+------+
| a    | b    | a    | c    |
+------+------+------+------+
|    2 | y    |    2 | z    |
| NULL | NULL |    3 | w    |
+------+------+------+------+

12. using子句可以被重寫成比較關(guān)聯(lián)列的 on 子句弧腥。即使 using 和 on 相似厦取，他們并不完全一樣。思考下面兩個查詢管搪。

a LEFT JOIN b USING (c1, c2, c3)
a LEFT JOIN b ON a.c1 = b.c1 AND a.c2 = b.c2 AND a.c3 = b.c3

關(guān)于決定哪行滿足 join 條件虾攻，兩個 join 語義上是相同的。
關(guān)于決定哪些列在 select *擴(kuò)展中展示更鲁，這兩個連接語義上不相同霎箍。using 連接選擇合并相關(guān)列的值，而 on 連接選擇所有表的所有列岁经。對于 using 連接朋沮，select * 選擇了這些值：

COALESCE(a.c1, b.c1), COALESCE(a.c2, b.c2), COALESCE(a.c3, b.c3)

對于 on 連接，select * 選擇了這些值：

a.c1, a.c2, a.c3, b.c1, b.c2, b.c3

對于一個內(nèi)部連接缀壤，coalesce(a.c1,b.c1)和 a.c1，b.c1兩者之一都相同纠亚，因為兩者的列有相同的值塘慕。對于外連接（如 left join）树碱，兩者之一的列會為空亏钩。列從結(jié)果中省略豁跑。

13. on 子句只能引用它的操作數(shù)僚害。
    如：

CREATE TABLE t1 (i1 INT);
CREATE TABLE t2 (i2 INT);
CREATE TABLE t3 (i3 INT);
SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;

語句因 on 語句中未知的列 i3失敗報錯衰抑，因為 i3是 t3的列，它不是 on 子句的操作數(shù)雪营。為了使連接可運行不翩，如下重寫語句：

SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);

14. Join比逗號有更高的優(yōu)先級，所以 連接表達(dá)式 t1,t2 join t3 可翻譯成(t1,(t2 join t3))指蚜，而不是((t1,t2) join t3)乞巧。這影響使用 on 子句的語句，因為子句只能引用連接的操作數(shù)的列摊鸡，并且優(yōu)先級影響那些操作數(shù)是是什么的解釋绽媒。
    如：

CREATE TABLE t1 (i1 INT, j1 INT);
CREATE TABLE t2 (i2 INT, j2 INT);
CREATE TABLE t3 (i3 INT, j3 INT);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t2 VALUES(1, 1);
INSERT INTO t3 VALUES(1, 1);
SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

join 優(yōu)先于逗號運算符，所以 on 子句的操作數(shù)是 t2和 t3免猾。因為 t1.i1不是兩者任一操作數(shù)的列是辕，結(jié)果就是錯誤： on 子句后未知列 t1.i1。
為了使連接可以運行猎提，使用這些策略的任意一個：
A. 顯示使用括號將兩張表化為一組获三，以便 on 子句的操作數(shù)為(t1,t2)和 t3：

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

B. 避免使用逗號運算符，使用 join 代替：

SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);

相同的優(yōu)先解釋也適用于混合逗號運算符和 inner join锨苏，cross join疙教，left join和 right join的語句，這些子句優(yōu)先于逗號操作符蚓炬。

15. 相比于 SQL:2003標(biāo)準(zhǔn)的 MySQL 的拓展是松逊，MySQL 允許你有資格使用 natural 或者 using 連接的和并列，而標(biāo)準(zhǔn)的不允許肯夏。

遺留問題：
1. join默認(rèn)執(zhí)行的是什么連接经宏？
2.內(nèi)連接，外連接?
3.嵌套 join?
4.table reference?

//TODO
總結(jié)一番：

參考：MySQL 官方文檔-Join Syntax