為了方便理解先新增幾個測試資料
mysql> create table t1 (m1 int, n1 char(1));
ERROR 4031 (HY000): The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.
No connection. Trying to reconnect...
Connection id: 12
Current database: ryan_demo_db
Query OK, 0 rows affected (1.28 sec)
mysql> create table t2 (m2 int, n2 char(1));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1 values(1,'a'),(2,'b'),(3,'c');
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into t2 values(2,'b'),(3,'c'),(4,'d');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+------+------+
| m1 | n1 |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from t2;
+------+------+
| m2 | n2 |
+------+------+
| 2 | b |
| 3 | c |
| 4 | d |
+------+------+
3 rows in set (0.00 sec)
基本概念
select * from t1,t2 where t1.m1 > 1 and t1.m1 = t2.m2 and t2.n2 < 'd';
這查詢中很明顯有三個過濾條件
首先第一個需要查詢的表叫驅動表，我們以t1開始查詢的話，t1就是驅動表。
從此表選取代價最小的存取方法(const、ref、range、index...等)去執行單表查詢，再來每從驅動表獲取一筆紀錄就立即去t2表尋找匹配的紀錄(也就是t2.m2=3及t2.m2=4兩筆紀錄)，因此可知道這兩表連接查詢共需要查詢1次t1表、2次t2表，也就是驅動表只需要存取一次，被驅動表可能需要存取多次。
內連接與外連接
mysql> create table student(
-> number int not null auto_increment comment '學號',
-> name varchar(5) comment '姓名',
-> major varchar(30) comment '專業',
-> primary key (number)
-> ) engine=InnoDB charset=utf8 comment '學生資訊表';
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> create table score(
-> number int comment '學號',
-> subject varchar(30) comment '科目',
-> score tinyint comment '成績',
-> primary key (number, subject)
-> ) engine=InnoDB charset=utf8 comment '學生成績表';
Query OK, 0 rows affected, 1 warning (0.02 sec)
新增一些測試資料如下
mysql> select * from student;
+----------+--------+--------+
| number | name | major |
+----------+--------+--------+
| 20210920 | 子瑜 | 射箭 |
| 20210921 | 均甯 | 演戲 |
| 20210922 | 水晶 | 演戲 |
+----------+--------+--------+
3 rows in set (0.05 sec)
mysql> select * from score;
+----------+-----------------------+-------+
| number | subject | score |
+----------+-----------------------+-------+
| 20210920 | 2016奧運 | 99 |
| 20210920 | 2020奧運 | 100 |
| 20210921 | 我在墾丁天氣晴 | 96 |
| 20210921 | 痞子英雄 | 97 |
+----------+-----------------------+-------+
4 rows in set (0.00 sec)
現在要想把女同學們的考試成績都查詢出來，就需要兩表連接如下：
mysql> select s1.number,s1.name,s2.subject,s2.score from student as s1, score as s2 where s1.number = s2.number;
+----------+--------+-----------------------+-------+
| number | name | subject | score |
+----------+--------+-----------------------+-------+
| 20210920 | 子瑜 | 2016奧運 | 99 |
| 20210920 | 子瑜 | 2020奧運 | 100 |
| 20210921 | 均甯 | 我在墾丁天氣晴 | 96 |
| 20210921 | 均甯 | 痞子英雄 | 97 |
+----------+--------+-----------------------+-------+
4 rows in set (0.01 sec)
發現有個問題，由於水晶同學沒有考試成績，所以不會顯示。但老師可能想要看到的是所有同學的考試成績。
因此為了解決這問題出現了內外連接的概念。
但這樣有個問題是我們有時候並不希望外連接中驅動表的所有紀錄都加到最終結果裡。這就困惑了，有時候需要加入結果，有時候又不想加入結果，那要怎麼做呢？
這邊就要透過把過濾條件分為兩種來實現
接下來我們直接以前面的例子來看看如果要看到所有同學的考試成績，該怎麼下語法呢？
mysql> select s1.number,s1.name,s2.subject,s2.score from student as s1 left join score as s2 on s1.number = s2.number;
+----------+--------+-----------------------+-------+
| number | name | subject | score |
+----------+--------+-----------------------+-------+
| 20210920 | 子瑜 | 2016奧運 | 99 |
| 20210920 | 子瑜 | 2020奧運 | 100 |
| 20210921 | 均甯 | 我在墾丁天氣晴 | 96 |
| 20210921 | 均甯 | 痞子英雄 | 97 |
| 20210922 | 水晶 | NULL | NULL |
+----------+--------+-----------------------+-------+
5 rows in set (0.01 sec)
這是一個左外連接。left join其實就是left[outer] join，我們通常把outer省略。
放在左邊的表就是外表(驅動表)，放在右邊的表就是內表(被驅動表)，以上面的語法來看t1就是外表，t2就是內表，對於外連接來說一定要有on來指出連接條件。
內連接的語法：
內連接與外連接的根本區別就是內連接驅動表中的紀錄不符合on子句中的條件時，不會把紀錄加入到最終結果中。以下都是內連接的寫法
直接看下面的例子大家就會清楚許多。
mysql> select * from t1;
+------+------+
| m1 | n1 |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from t2;
+------+------+
| m2 | n2 |
+------+------+
| 2 | b |
| 3 | c |
| 4 | d |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from t1 inner join t2 on t1.m1 = t2.m2;
+------+------+------+------+
| m1 | n1 | m2 | n2 |
+------+------+------+------+
| 2 | b | 2 | b |
| 3 | c | 3 | c |
+------+------+------+------+
2 rows in set (0.00 sec)
mysql> select * from t1 left join t2 on t1.m1 = t2.m2;
+------+------+------+------+
| m1 | n1 | m2 | n2 |
+------+------+------+------+
| 1 | a | NULL | NULL |
| 2 | b | 2 | b |
| 3 | c | 3 | c |
+------+------+------+------+
3 rows in set (0.00 sec)
mysql> select * from t1 right join t2 on t1.m1 = t2.m2;
+------+------+------+------+
| m1 | n1 | m2 | n2 |
+------+------+------+------+
| 2 | b | 2 | b |
| 3 | c | 3 | c |
| NULL | NULL | 4 | d |
+------+------+------+------+
3 rows in set (0.01 sec)
再來要了解的是一些重要觀念：
巢狀結構迴圈連接
我們已經知道會先選取驅動表，以相關條件過濾後再分別到被驅動表中一筆一筆尋找匹配的紀錄，這個過程就像是一個巢狀結構的迴圈，這是最簡單也最慢的連接查詢演算法。
使用索引加快速度
盡量不要使用*作為查詢列表，有必要且有索引的列再作為查詢條件的話可以大幅地加快速度。
基於區塊的巢狀結構迴圈連接
Mysql工程師提出join buffer的概念，先申請一塊記憶體，在驅動表的結果紀錄儲存在裡面，一次性的比對被驅動表與其裡面的多筆紀錄，大幅地加快比對速度。此外由於有專屬的記憶體空間也可以避免掉可能的大量I/O（當資料太多，記憶體不足，所以會分次從磁碟上讀取資料到記憶體好多次，造成大量I/O）。要特別注意的是join buffer不會存放驅動表紀錄的所有列，只有查詢列表中的列和過濾條件中的列才會被放進去，所以這也再次提醒我們非必要不要把*作為查詢列表，盡量以必要且有索引的列放入就好。
join buffe的大小可以透過啟動選項或系統變數join_buffer_size進行設定，預設為256kb。
iThome鐵人賽
看影片追技術