淺析Mysql Join語法和機能優化。本站提示廣大學習愛好者：（淺析Mysql Join語法和機能優化）文章只能為提供參考，不一定能成為您想要的結果。以下是淺析Mysql Join語法和機能優化正文
一．Join語法概述
join 用於多表中字段之間的接洽，語法以下：
... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona
table1:左表；table2:右表。
JOIN 依照功效年夜致分為以下三類：
INNER JOIN（內銜接,或等值銜接）：獲得兩個表中存在銜接婚配關系的記載。
LEFT JOIN（左銜接）：獲得左表（table1）完整記載，等於右表（table2）並沒有對應婚配記載。
RIGHT JOIN（右銜接）：與 LEFT JOIN 相反，獲得右表（table2）完整記載，等於左表（table1）並沒有婚配對應記載。
留意：mysql不支撐Full join,不外可以經由過程UNION 症結字來歸並 LEFT JOIN 與 RIGHT JOIN來模仿FULL join.
接上去給出一個列子用於說明上面幾種分類。以下兩個表(A,B)
mysql> select A.id,A.name,B.name from A,B where A.id=B.id;
+----+-----------+-------------+
| id | name | name |
+----+-----------+-------------+
| 1 | Pirate | Rutabaga |
| 2 | Monkey | Pirate |
| 3 | Ninja | Darth Vader |
| 4 | Spaghetti | Ninja |
+----+-----------+-------------+
4 rows in set (0.00 sec)
二.Inner join
內銜接，也叫等值銜接，inner join發生同時相符A和B的一組數據。
mysql> select * from A inner join B on A.name = B.name;
+----+--------+----+--------+
| id | name | id | name |
+----+--------+----+--------+
| 1 | Pirate | 2 | Pirate |
| 3 | Ninja | 4 | Ninja |
+----+--------+----+--------+
三.Left join
mysql> select * from A left join B on A.name = B.name;
#或許：select * from A left outer join B on A.name = B.name;
+----+-----------+------+--------+
| id | name | id | name |
+----+-----------+------+--------+
| 1 | Pirate | 2 | Pirate |
| 2 | Monkey | NULL | NULL |
| 3 | Ninja | 4 | Ninja |
| 4 | Spaghetti | NULL | NULL |
+----+-----------+------+--------+
4 rows in set (0.00 sec)
left join,（或left outer join:在Mysql中二者等價，推舉應用left join.）左銜接從左表(A)發生一套完全的記載,與婚配的記載(右表(B)) .假如沒有婚配,右邊將包括null。
假如想只從左表(A)中發生一套記載，但不包括右表(B)的記載，可以經由過程設置where語句來履行，以下：
mysql> select * from A left join B on A.name=B.name where A.id is null or B.id is null;
+----+-----------+------+------+
| id | name | id | name |
+----+-----------+------+------+
| 2 | Monkey | NULL | NULL |
| 4 | Spaghetti | NULL | NULL |
+----+-----------+------+------+
2 rows in set (0.00 sec)
同理，還可以模仿inner join. 以下：
mysql> select * from A left join B on A.name=B.name where A.id is not null and B.id is not null;
+----+--------+------+--------+
| id | name | id | name |
+----+--------+------+--------+
| 1 | Pirate | 2 | Pirate |
| 3 | Ninja | 4 | Ninja |
+----+--------+------+--------+
2 rows in set (0.00 sec)
求差集：
依據下面的例子可以求差集，以下：
SELECT * FROM A LEFT JOIN B ON A.name = B.name
WHERE B.id IS NULL
union
SELECT * FROM A right JOIN B ON A.name = B.name
WHERE A.id IS NULL;
# 成果
+------+-----------+------+-------------+
| id | name | id | name |
+------+-----------+------+-------------+
| 2 | Monkey | NULL | NULL |
| 4 | Spaghetti | NULL | NULL |
| NULL | NULL | 1 | Rutabaga |
| NULL | NULL | 3 | Darth Vader |
+------+-----------+------+-------------+
四.Right join
mysql> select * from A right join B on A.name = B.name;
+------+--------+----+-------------+
| id | name | id | name |
+------+--------+----+-------------+
| NULL | NULL | 1 | Rutabaga |
| 1 | Pirate | 2 | Pirate |
| NULL | NULL | 3 | Darth Vader |
| 3 | Ninja | 4 | Ninja |
+------+--------+----+-------------+
4 rows in set (0.00 sec)
同left join。
五.Cross join
cross join：穿插銜接，獲得的成果是兩個表的乘積，即笛卡爾積
笛卡爾（Descartes）乘積又叫直積。假定聚集A={a,b}，聚集B={0,1,2}，則兩個聚集的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以擴大到多個聚集的情形。相似的例子有，假如A表現某黉捨先生的聚集，B表現該黉捨一切課程的聚集，則A與B的笛卡爾積表現一切能夠的選課情形。
mysql> select * from A cross join B;
+----+-----------+----+-------------+
| id | name | id | name |
+----+-----------+----+-------------+
| 1 | Pirate | 1 | Rutabaga |
| 2 | Monkey | 1 | Rutabaga |
| 3 | Ninja | 1 | Rutabaga |
| 4 | Spaghetti | 1 | Rutabaga |
| 1 | Pirate | 2 | Pirate |
| 2 | Monkey | 2 | Pirate |
| 3 | Ninja | 2 | Pirate |
| 4 | Spaghetti | 2 | Pirate |
| 1 | Pirate | 3 | Darth Vader |
| 2 | Monkey | 3 | Darth Vader |
| 3 | Ninja | 3 | Darth Vader |
| 4 | Spaghetti | 3 | Darth Vader |
| 1 | Pirate | 4 | Ninja |
| 2 | Monkey | 4 | Ninja |
| 3 | Ninja | 4 | Ninja |
| 4 | Spaghetti | 4 | Ninja |
+----+-----------+----+-------------+
16 rows in set (0.00 sec)
#再履行：mysql> select * from A inner join B; 試一試
#在履行mysql> select * from A cross join B on A.name = B.name; 試一試
現實上，在 MySQL 中（僅限於 MySQL） CROSS JOIN 與 INNER JOIN 的表示是一樣的，在不指定 ON 前提獲得的成果都是笛卡爾積，反之獲得兩個表完整婚配的成果。 INNER JOIN 與 CROSS JOIN 可以省略 INNER 或 CROSS 症結字，是以上面的 SQL 後果是一樣的：
... FROM table1 INNER JOIN table2
... FROM table1 CROSS JOIN table2
... FROM table1 JOIN table2
六.Full join
mysql> select * from A left join B on B.name = A.name
-> union
-> select * from A right join B on B.name = A.name;
+------+-----------+------+-------------+
| id | name | id | name |
+------+-----------+------+-------------+
| 1 | Pirate | 2 | Pirate |
| 2 | Monkey | NULL | NULL |
| 3 | Ninja | 4 | Ninja |
| 4 | Spaghetti | NULL | NULL |
| NULL | NULL | 1 | Rutabaga |
| NULL | NULL | 3 | Darth Vader |
+------+-----------+------+-------------+
6 rows in set (0.00 sec)
全銜接發生的一切記載（兩邊婚配記載）在表A和表B。假如沒有婚配,則對面將包括null。
七.機能優化
1.顯示(explicit) inner join VS 隱式(implicit) inner join
如：
select * from
table a inner join table b
on a.id = b.id;
VS
select a.*, b.*
from table a, table b
where a.id = b.id;
我在數據庫中比擬(10w數據)得之，它們用時簡直雷同，第一個是顯示的inner join，後一個是隱式的inner join。
2.left join/right join VS inner join
盡可能用inner join.防止 LEFT JOIN 和 NULL.
在應用left join（或right join）時，應當清晰的曉得以下幾點：
(1). on與 where的履行次序
ON 前提（“A LEFT JOIN B ON 前提表達式”中的ON）用來決議若何從 B 表中檢索數據行。假如 B 表中沒有任何一行數據婚配 ON 的前提,將會額定生成一行一切列為 NULL 的數據,在婚配階段 WHERE 子句的前提都不會被應用。僅在婚配階段完成今後，WHERE 子句前提才會被應用。它將從婚配階段發生的數據中檢索過濾。
所以我們要留意：在應用Left (right) join的時刻，必定要在先給出盡量多的婚配知足前提，削減Where的履行。如：
PASS
select * from A
inner join B on B.name = A.name
left join C on C.name = B.name
left join D on D.id = C.id
where C.status>1 and D.status=1;
Great
select * from A
inner join B on B.name = A.name
left join C on C.name = B.name and C.status>1
left join D on D.id = C.id and D.status=1
從下面例子可以看出，盡量知足ON的前提，而罕用Where的前提。從履行機能來看第二個明顯加倍省時。
(2).留意ON 子句和 WHERE 子句的分歧
如作者舉了一個列子：
mysql> SELECT * FROM product LEFT JOIN product_details
ON (product.id = product_details.id)
AND product_details.id=2;
+----+--------+------+--------+-------+
| id | amount | id | weight | exist |
+----+--------+------+--------+-------+
| 1 | 100 | NULL | NULL | NULL |
| 2 | 200 | 2 | 22 | 0 |
| 3 | 300 | NULL | NULL | NULL |
| 4 | 400 | NULL | NULL | NULL |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM product LEFT JOIN product_details
ON (product.id = product_details.id)
WHERE product_details.id=2;
+----+--------+----+--------+-------+
| id | amount | id | weight | exist |
+----+--------+----+--------+-------+
| 2 | 200 | 2 | 22 | 0 |
+----+--------+----+--------+-------+
1 row in set (0.01 sec)
從上可知，第一條查詢應用 ON 前提決議了從 LEFT JOIN的 product_details表中檢索相符的一切數據行。第二條查詢做了簡略的LEFT JOIN，然後應用 WHERE 子句從 LEFT JOIN的數據中過濾失落不相符前提的數據行。
(3).盡可能防止子查詢，而用join
常常機能這玩藝兒，更多時刻表現在數據量比擬年夜的時刻，此時，我們應當防止龐雜的子查詢。以下：
PASS
insert into t1(a1) select b1 from t2 where not exists(select 1 from t1 where t1.id = t2.r_id);
Great
insert into t1(a1)
select b1 from t2
left join (select distinct t1.id from t1 ) t1 on t1.id = t2.r_id
where t1.id is null;
