概念
INNER JOIN(内連接配接)：擷取兩個表中字段比對關系的記錄。也就是隻會傳回共有的内容。
LEFT JOIN(左連接配接)：擷取左表所有記錄，即使右表沒有對應比對的記錄。
RIGHT JOIN(右連接配接)： 擷取右表所有記錄，即使左表沒有對應比對的記錄。
示例
先在資料庫中建立兩張表student和score，具體内容如下：
【student】
mysql> select * from student;
--------------
select * from student
--------------
+----+---------------------+------+-------+------------+-----------+
| id | name | sex | birth | department | address |
+----+---------------------+------+-------+------------+-----------+
| 1 | RooneyMara | F | 1985 | Psychology | American |
| 2 | ChrisHemsworth | M | 1983 | CS | Australia |
| 3 | EllenPage | F | 1987 | Music | Canada |
| 4 | TomHolland | M | 1996 | CS | England |
| 5 | ScarlettJohansson | F | 1984 | Music | American |
| 6 | BenedictCumberbatch | M | 1976 | Psychology | England |
| 7 | EvaGreen | F | 1980 | Math | France |
+----+---------------------+------+-------+------------+-----------+
7 rows in set (0.00 sec)
【score】
mysql> select * from score;
--------------
select * from score
--------------
+----+--------+------------+-------+
| id | stu_id | c_name | grade |
+----+--------+------------+-------+
| 1 | 1 | Psychology | 98 |
| 2 | 1 | Music | 80 |
| 3 | 2 | Psychology | 65 |
| 4 | 2 | CS | 88 |
| 5 | 3 | CS | 95 |
| 6 | 4 | Psychology | 70 |
| 7 | 4 | Music | 92 |
| 8 | 5 | Music | 94 |
| 9 | 6 | Psychology | 90 |
| 10 | 6 | CS | 85 |
| 11 | 8 | Music | 91 |
+----+--------+------------+-------+
11 rows in set (0.00 sec)
内連接配接
查詢student表中的所有個人資訊及score表中的c_name,grade
mysql> select a.*,c_name,grade from student a join score b on a.id=b.stu_id;
--------------
select a.*,c_name,grade from student a join score b on a.id=b.stu_id
--------------
+----+---------------------+------+-------+------------+-----------+------------+-------+
| id | name | sex | birth | department | address | c_name | grade |
+----+---------------------+------+-------+------------+-----------+------------+-------+
| 1 | RooneyMara | F | 1985 | Psychology | American | Psychology | 98 |
| 1 | RooneyMara | F | 1985 | Psychology | American | Music | 80 |
| 2 | ChrisHemsworth | M | 1983 | CS | Australia | Psychology | 65 |
| 2 | ChrisHemsworth | M | 1983 | CS | Australia | CS | 88 |
| 3 | EllenPage | F | 1987 | Music | Canada | CS | 95 |
| 4 | TomHolland | M | 1996 | CS | England | Psychology | 70 |
| 4 | TomHolland | M | 1996 | CS | England | Music | 92 |
| 5 | ScarlettJohansson | F | 1984 | Music | American | Music | 94 |
| 6 | BenedictCumberbatch | M | 1976 | Psychology | England | Psychology | 90 |
| 6 | BenedictCumberbatch | M | 1976 | Psychology | England | CS | 85 |
+----+---------------------+------+-------+------------+-----------+------------+-------+
10 rows in set (0.00 sec)
以上語句等價于：
mysql> select a.*,c_name,grade from student a,score b where a.id=b.stu_id;
--------------
select a.*,c_name,grade from student a,score b where a.id=b.stu_id
--------------
+----+---------------------+------+-------+------------+-----------+------------+-------+
| id | name | sex | birth | department | address | c_name | grade |
+----+---------------------+------+-------+------------+-----------+------------+-------+
| 1 | RooneyMara | F | 1985 | Psychology | American | Psychology | 98 |
| 1 | RooneyMara | F | 1985 | Psychology | American | Music | 80 |
| 2 | ChrisHemsworth | M | 1983 | CS | Australia | Psychology | 65 |
| 2 | ChrisHemsworth | M | 1983 | CS | Australia | CS | 88 |
| 3 | EllenPage | F | 1987 | Music | Canada | CS | 95 |
| 4 | TomHolland | M | 1996 | CS | England | Psychology | 70 |
| 4 | TomHolland | M | 1996 | CS | England | Music | 92 |
| 5 | ScarlettJohansson | F | 1984 | Music | American | Music | 94 |
| 6 | BenedictCumberbatch | M | 1976 | Psychology | England | Psychology | 90 |
| 6 | BenedictCumberbatch | M | 1976 | Psychology | England | CS | 85 |
+----+---------------------+------+-------+------------+-----------+------------+-------+
10 rows in set (0.00 sec)
左連接配接
student表中id為7的資料，在score中沒有對應的内容。是以最後一條查詢結果c_name,grade對應内容為null。
mysql> select a.*,c_name,grade from student a left join score b on a.id=b.stu_id;
--------------
select a.*,c_name,grade from student a left join score b on a.id=b.stu_id
--------------
+----+---------------------+------+-------+------------+-----------+------------+-------+
| id | name | sex | birth | department | address | c_name | grade |
+----+---------------------+------+-------+------------+-----------+------------+-------+
| 1 | RooneyMara | F | 1985 | Psychology | American | Psychology | 98 |
| 1 | RooneyMara | F | 1985 | Psychology | American | Music | 80 |
| 2 | ChrisHemsworth | M | 1983 | CS | Australia | Psychology | 65 |
| 2 | ChrisHemsworth | M | 1983 | CS | Australia | CS | 88 |
| 3 | EllenPage | F | 1987 | Music | Canada | CS | 95 |
| 4 | TomHolland | M | 1996 | CS | England | Psychology | 70 |
| 4 | TomHolland | M | 1996 | CS | England | Music | 92 |
| 5 | ScarlettJohansson | F | 1984 | Music | American | Music | 94 |
| 6 | BenedictCumberbatch | M | 1976 | Psychology | England | Psychology | 90 |
| 6 | BenedictCumberbatch | M | 1976 | Psychology | England | CS | 85 |
| 7 | EvaGreen | F | 1980 | Math | France | NULL | NULL |
+----+---------------------+------+-------+------------+-----------+------------+-------+
11 rows in set (0.00 sec)
右連接配接
score表中id為11的資料，在student中沒有對應的内容，是以最後一條查詢結果id,name,sex等對應内容為null。
mysql> select a.*,c_name,grade from student a right join score b on a.id=b.stu_id;
--------------
select a.*,c_name,grade from student a right join score b on a.id=b.stu_id
--------------
+------+---------------------+------+-------+------------+-----------+------------+-------+
| id | name | sex | birth | department | address | c_name | grade |
+------+---------------------+------+-------+------------+-----------+------------+-------+
| 1 | RooneyMara | F | 1985 | Psychology | American | Psychology | 98 |
| 1 | RooneyMara | F | 1985 | Psychology | American | Music | 80 |
| 2 | ChrisHemsworth | M | 1983 | CS | Australia | Psychology | 65 |
| 2 | ChrisHemsworth | M | 1983 | CS | Australia | CS | 88 |
| 3 | EllenPage | F | 1987 | Music | Canada | CS | 95 |
| 4 | TomHolland | M | 1996 | CS | England | Psychology | 70 |
| 4 | TomHolland | M | 1996 | CS | England | Music | 92 |
| 5 | ScarlettJohansson | F | 1984 | Music | American | Music | 94 |
| 6 | BenedictCumberbatch | M | 1976 | Psychology | England | Psychology | 90 |
| 6 | BenedictCumberbatch | M | 1976 | Psychology | England | CS | 85 |
| NULL | NULL | NULL | NULL | NULL | NULL | Music | 91 |
+------+---------------------+------+-------+------------+-----------+------------+-------+
11 rows in set (0.00 sec)