MySQL表中的連接方式其實非常簡單，這里就簡單的羅列出他們的特點。
表的連接（JOIN）可以分為內連接（JOIN/INNER JOIN）和外連接（LEFT JOIN/RIGHT JOIN）。
首先我們看一下我們本次演示的兩個表：
mysql> SELECT * FROM student;
+------+----------+------+------+
| s_id | s_name | age | c_id |
+------+----------+------+------+
| 1 | xiaoming | 13 | 1 |
| 2 | xiaohong | 41 | 4 |
| 3 | xiaoxia | 22 | 3 |
| 4 | xiaogang | 32 | 1 |
| 5 | xiaoli | 41 | 2 |
| 6 | wangwu | 13 | 2 |
| 7 | lisi | 22 | 3 |
| 8 | zhangsan | 11 | 9 |
+------+----------+------+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM class;
+------+---------+-------+
| c_id | c_name | count |
+------+---------+-------+
| 1 | MATH | 65 |
| 2 | CHINESE | 70 |
| 3 | ENGLISH | 50 |
| 4 | HISTORY | 30 |
| 5 | BIOLOGY | 40 |
+------+---------+-------+
5 rows in set (0.00 sec)
首先，表要能連接的前提就是兩個表中有相同的可以比較的列。
1.內連接
mysql> SELECT * FROM student INNER JOIN class ON student.c_id = class.c_id;
+------+----------+------+------+------+---------+-------+
| s_id | s_name | age | c_id | c_id | c_name | count |
+------+----------+------+------+------+---------+-------+
| 1 | xiaoming | 13 | 1 | 1 | MATH | 65 |
| 2 | xiaohong | 41 | 4 | 4 | HISTORY | 30 |
| 3 | xiaoxia | 22 | 3 | 3 | ENGLISH | 50 |
| 4 | xiaogang | 32 | 1 | 1 | MATH | 65 |
| 5 | xiaoli | 41 | 2 | 2 | CHINESE | 70 |
| 6 | wangwu | 13 | 2 | 2 | CHINESE | 70 |
| 7 | lisi | 22 | 3 | 3 | ENGLISH | 50 |
+------+----------+------+------+------+---------+-------+
7 rows in set (0.00 sec)
簡單的講，內連接就是把兩個表中符合條件的行的所有數據一起展示出來，即如果不符合條件，即在表A中找得到但是在B中沒有（或者相反）的數據不予以顯示。
2.外連接
mysql> SELECT * FROM student LEFT JOIN class ON student.c_id = class.c_id;
+------+----------+------+------+------+---------+-------+
| s_id | s_name | age | c_id | c_id | c_name | count |
+------+----------+------+------+------+---------+-------+
| 1 | xiaoming | 13 | 1 | 1 | MATH | 65 |
| 2 | xiaohong | 41 | 4 | 4 | HISTORY | 30 |
| 3 | xiaoxia | 22 | 3 | 3 | ENGLISH | 50 |
| 4 | xiaogang | 32 | 1 | 1 | MATH | 65 |
| 5 | xiaoli | 41 | 2 | 2 | CHINESE | 70 |
| 6 | wangwu | 13 | 2 | 2 | CHINESE | 70 |
| 7 | lisi | 22 | 3 | 3 | ENGLISH | 50 |
| 8 | zhangsan | 11 | 9 | NULL | NULL | NULL |
+------+----------+------+------+------+---------+-------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM student RIGHT JOIN class ON student.c_id = class.c_id;
+------+----------+------+------+------+---------+-------+
| s_id | s_name | age | c_id | c_id | c_name | count |
+------+----------+------+------+------+---------+-------+
| 1 | xiaoming | 13 | 1 | 1 | MATH | 65 |
| 4 | xiaogang | 32 | 1 | 1 | MATH | 65 |
| 5 | xiaoli | 41 | 2 | 2 | CHINESE | 70 |
| 6 | wangwu | 13 | 2 | 2 | CHINESE | 70 |
| 3 | xiaoxia | 22 | 3 | 3 | ENGLISH | 50 |
| 7 | lisi | 22 | 3 | 3 | ENGLISH | 50 |
| 2 | xiaohong | 41 | 4 | 4 | HISTORY | 30 |
| NULL | NULL | NULL | NULL | 5 | BIOLOGY | 40 |
+------+----------+------+------+------+---------+-------+
8 rows in set (0.00 sec)
上面分別展示了外連接的兩種情況：左連接和右連接。這兩種幾乎是一樣的，唯一的區別就是左連接的主表是左邊的表，右連接的主表是右邊的表。而外連接與內連接不同的地方就是它會將主表的所有行都予以顯示，而在主表中有，其他表中沒有的數據用NULL代替。
總結
到此這篇關于MySQL中表的幾種連接方式的文章就介紹到這了,更多相關MySQL表的連接方式內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- MySQL中基本的多表連接查詢教程
- mysql delete 多表連接刪除功能
- 圖文詳解MySQL中兩表關聯的連接表如何創建索引
- MySQL表LEFT JOIN左連接與RIGHT JOIN右連接的實例教程
- mysql多表連接查詢實例講解
- MySQL中對表連接查詢的簡單優化教程
- mysql三張表連接建立視圖
- 詳解MySQL數據庫--多表查詢--內連接，外連接，子查詢，相關子查詢
- MySQL的LEFT JOIN表連接的進階學習教程
- MySQL多表連接的入門實例教程
