MySQL中各種連接操作

以person和address兩張表為例進(jìn)行說(shuō)明逻族，兩表結(jié)構(gòu)如下：

select * from person;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | AAA  |   18 |
|  2 | BBB  |   25 |
|  3 | CCC  |   21 |
+----+------+------+
3 rows in set (0.00 sec)

select * from address;
+----+----------+
| id | address  |
+----+----------+
|  1 | HOME_AAA |
|  2 | HOME_BBB |
|  4 | HOME_DDD |
+----+----------+
3 rows in set (0.08 sec)

1.內(nèi)連接

1.1 自然連接（natural join）

自然連接只考慮兩個(gè)關(guān)系中在共同屬性上取值相同的元組對(duì)蜻底。結(jié)果中無(wú)null，不需要使用修飾詞限制連接屬性。
在本例中薄辅，共同屬性為id要拂，結(jié)果如下：

select * from person natural join address;
+----+------+------+----------+
| id | name | age  | address  |
+----+------+------+----------+
|  1 | AAA  |   18 | HOME_AAA |
|  2 | BBB  |   25 | HOME_BBB |
+----+------+------+----------+
2 rows in set (0.05 sec)

1.2 內(nèi)連接（inner join）

默認(rèn)情況下inner join和join兩種語(yǔ)法等價(jià)。
對(duì)于內(nèi)連接站楚，必須使用using或on指定連接屬性/條件脱惰，否則產(chǎn)生的結(jié)果與交叉連接相同（結(jié)果均為兩個(gè)表的笛卡爾積），在后文中介紹窿春。

使用using指定連接屬性拉一，結(jié)果中只包含1個(gè)id屬性：

select * from person join address using(id);
+----+------+------+----------+
| id | name | age  | address  |
+----+------+------+----------+
|  1 | AAA  |   18 | HOME_AAA |
|  2 | BBB  |   25 | HOME_BBB |
+----+------+------+----------+
2 rows in set (0.00 sec)

使用on指定連接條件，結(jié)果中包含2個(gè)id屬性：

select * from person inner join address on person.id = address.id;
+----+------+------+----+----------+
| id | name | age  | id | address  |
+----+------+------+----+----------+
|  1 | AAA  |   18 |  1 | HOME_AAA |
|  2 | BBB  |   25 |  2 | HOME_BBB |
+----+------+------+----+----------+
2 rows in set (0.00 sec)

2.外連接

2.1 左外連接（left outer join）

默認(rèn)情況下left outer join和left join兩種語(yǔ)法等價(jià)旧乞。
以左邊的表為主蔚润，在右邊的表中找到所有滿足條件的元素，并把他們連接起來(lái)尺栖，如果沒(méi)有對(duì)應(yīng)的元素嫡纠，則在相應(yīng)位置上的值為null。
特別注意：使用外連接必須使用using或on指定連接屬性或連接條件延赌，否則會(huì)報(bào)錯(cuò)除盏。

使用using指定連接屬性，結(jié)果中只包含1個(gè)id屬性：

select * from person left outer join address using(id);
+----+------+------+----------+
| id | name | age  | address  |
+----+------+------+----------+
|  1 | AAA  |   18 | HOME_AAA |
|  2 | BBB  |   25 | HOME_BBB |
|  3 | CCC  |   21 | NULL     |
+----+------+------+----------+
3 rows in set (0.00 sec)

使用on指定連接條件挫以，結(jié)果中包含2個(gè)id屬性：

select * from person left outer join address on person.id = address.id;
+----+------+------+------+----------+
| id | name | age  | id   | address  |
+----+------+------+------+----------+
|  1 | AAA  |   18 |    1 | HOME_AAA |
|  2 | BBB  |   25 |    2 | HOME_BBB |
|  3 | CCC  |   21 | NULL | NULL     |
+----+------+------+------+----------+
3 rows in set (0.00 sec)

2.2 右外連接（right outer join）

默認(rèn)情況下right outer join和right join兩種語(yǔ)法等價(jià)者蠕。
與左外連接相反，以右邊的表為主掐松，在左邊的表中找到所有滿足條件的元素踱侣，并把他們連接起來(lái)，如果沒(méi)有對(duì)應(yīng)的元素甩栈，則在相應(yīng)位置上的值為null泻仙。
特別注意：使用外連接必須使用using或on指定連接屬性或連接條件糕再，否則會(huì)報(bào)錯(cuò)量没。

使用using指定連接屬性，結(jié)果中只包含1個(gè)id屬性：

select * from person right outer join address using(id);
+----+----------+------+------+
| id | address  | name | age  |
+----+----------+------+------+
|  1 | HOME_AAA | AAA  |   18 |
|  2 | HOME_BBB | BBB  |   25 |
|  4 | HOME_DDD | NULL | NULL |
+----+----------+------+------+
3 rows in set (0.04 sec)

使用on指定連接條件突想，結(jié)果中包含2個(gè)id屬性：

select * from person right outer join address on person.id = address.id;
+------+------+------+----+----------+
| id   | name | age  | id | address  |
+------+------+------+----+----------+
|    1 | AAA  |   18 |  1 | HOME_AAA |
|    2 | BBB  |   25 |  2 | HOME_BBB |
| NULL | NULL | NULL |  4 | HOME_DDD |
+------+------+------+----+----------+
3 rows in set (0.02 sec)

2.3 全外連接（full outer join）

全外連接顯示所有表的行殴蹄、列，條件不匹配的值為皆為null猾担。
特別注意：在MySQL中不支持全外連接操作袭灯，可通過(guò)Union左外連接和右外連接來(lái)實(shí)現(xiàn)。

使用on指定連接條件绑嘹，結(jié)果中包含2個(gè)id屬性：

mysql> select * from person left outer join address on person.id = address.id
    -> union
    -> select * from person right outer join address on person.id = address.id;
+------+------+------+------+----------+
| id   | name | age  | id   | address  |
+------+------+------+------+----------+
|    1 | AAA  |   18 |    1 | HOME_AAA |
|    2 | BBB  |   25 |    2 | HOME_BBB |
|    3 | CCC  |   21 | NULL | NULL     |
| NULL | NULL | NULL |    4 | HOME_DDD |
+------+------+------+------+----------+
4 rows in set (0.40 sec)

3.交叉連接（cross join）

交叉連接所返回結(jié)果即為笛卡爾積稽荧，即：對(duì)于兩個(gè)不同的集合A和B，對(duì)于A中的每一個(gè)元素工腋，都有對(duì)于在B中的所有元素做連接運(yùn)算姨丈。因此對(duì)于兩個(gè)元組分別為m畅卓，n的表，笛卡爾積后得到的元組個(gè)數(shù)為m x n蟋恬。
由于交叉連接與笛卡爾積結(jié)果相同翁潘，因此如下兩種寫法相同：

寫法1：交叉連接cross join

select * from person cross join address;
+----+------+------+----+----------+
| id | name | age  | id | address  |
+----+------+------+----+----------+
|  1 | AAA  |   18 |  1 | HOME_AAA |
|  2 | BBB  |   25 |  1 | HOME_AAA |
|  3 | CCC  |   21 |  1 | HOME_AAA |
|  1 | AAA  |   18 |  2 | HOME_BBB |
|  2 | BBB  |   25 |  2 | HOME_BBB |
|  3 | CCC  |   21 |  2 | HOME_BBB |
|  1 | AAA  |   18 |  4 | HOME_DDD |
|  2 | BBB  |   25 |  4 | HOME_DDD |
|  3 | CCC  |   21 |  4 | HOME_DDD |
+----+------+------+----+----------+
9 rows in set (0.00 sec)

寫法2：笛卡爾積

select * from person,address;
+----+------+------+----+----------+
| id | name | age  | id | address  |
+----+------+------+----+----------+
|  1 | AAA  |   18 |  1 | HOME_AAA |
|  2 | BBB  |   25 |  1 | HOME_AAA |
|  3 | CCC  |   21 |  1 | HOME_AAA |
|  1 | AAA  |   18 |  2 | HOME_BBB |
|  2 | BBB  |   25 |  2 | HOME_BBB |
|  3 | CCC  |   21 |  2 | HOME_BBB |
|  1 | AAA  |   18 |  4 | HOME_DDD |
|  2 | BBB  |   25 |  4 | HOME_DDD |
|  3 | CCC  |   21 |  4 | HOME_DDD |
+----+------+------+----+----------+
9 rows in set (0.00 sec)

當(dāng)使用內(nèi)鏈接不加連接屬性或條件時(shí)，結(jié)果也為笛卡爾積：

select * from person inner join address;
+----+------+------+----+----------+
| id | name | age  | id | address  |
+----+------+------+----+----------+
|  1 | AAA  |   18 |  1 | HOME_AAA |
|  2 | BBB  |   25 |  1 | HOME_AAA |
|  3 | CCC  |   21 |  1 | HOME_AAA |
|  1 | AAA  |   18 |  2 | HOME_BBB |
|  2 | BBB  |   25 |  2 | HOME_BBB |
|  3 | CCC  |   21 |  2 | HOME_BBB |
|  1 | AAA  |   18 |  4 | HOME_DDD |
|  2 | BBB  |   25 |  4 | HOME_DDD |
|  3 | CCC  |   21 |  4 | HOME_DDD |
+----+------+------+----+----------+
9 rows in set (0.09 sec)

4.連接語(yǔ)句中on與where的區(qū)別

on和where的查詢順序不同歼争。
標(biāo)準(zhǔn)查詢關(guān)鍵字執(zhí)行順序?yàn)椋篺rom > where > group by > having > order by
在連接語(yǔ)句中拜马，join是在from范圍內(nèi)，而on條件是對(duì)join進(jìn)行條件過(guò)濾沐绒，所以會(huì)先on條件篩選表俩莽，然后再做join；對(duì)于where來(lái)說(shuō)乔遮，是在join執(zhí)行后生成的臨時(shí)表上再進(jìn)行條件過(guò)濾豹绪。
以right join的on條件和where條件進(jìn)行舉例說(shuō)明：

例1：on條件中增加對(duì)左表的限制條件

結(jié)果：返回右表的全部記錄，左表的篩選條件生效

select * from person right join address on person.id = address.id and person.age<20;
+------+------+------+----+----------+
| id   | name | age  | id | address  |
+------+------+------+----+----------+
|    1 | AAA  |   18 |  1 | HOME_AAA |
| NULL | NULL | NULL |  2 | HOME_BBB |
| NULL | NULL | NULL |  4 | HOME_DDD |
+------+------+------+----+----------+
3 rows in set (0.00 sec)

例2：on條件后增加where限制條件

結(jié)果：where后邊的條件是對(duì)最終的臨時(shí)表進(jìn)行篩選申眼，on后邊的條件是作為匹配條件進(jìn)行篩選

select * from person right join address on person.id = address.id where person.age<20;
+------+------+------+----+----------+
| id   | name | age  | id | address  |
+------+------+------+----+----------+
|    1 | AAA  |   18 |  1 | HOME_AAA |
+------+------+------+----+----------+
1 row in set (0.03 sec)

附錄：SQL連接方式圖片說(shuō)明