【Mysql】圖解左、右、內與全連線

一、前言

使用學生表與成績表來演示Mysql中的各種連線查詢

學生表的建表語句如下：

CREATE TABLE student(
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增序號',
  st_id int(11) DEFAULT NULL COMMENT '學生id',
  st_name varchar(255) DEFAULT NULL COMMENT '學生姓名',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB;

成績表的建表語句如下：

CREATE TABLE score(
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
  st_id int(11) DEFAULT NULL COMMENT '學生id',
  subject varchar(255) DEFAULT NULL COMMENT '學科名稱',
  grade int(11) DEFAULT NULL COMMENT '學科成績',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB ;

初始資料如下：

---

二、內連線

按照關聯欄位取出兩個表中的記錄，保留的是兩個表的交集。

例如：

SELECT
	student.st_id,
	student.st_name,
	score.subject,
	score.grade 
FROM
	student
	INNER JOIN score ON student.st_id = score.st_id;

執行結果：

對於關聯欄位st_id，左表與右表都有1001與1002。

---

三、左連線

按照關聯欄位取出兩個表中的記錄，保留左表所有的記錄，以及滿足連線條件的右表記錄，右表中不滿足連線條件的會被置為null。

例如：

SELECT
	student.st_id,
	student.st_name,
	score.subject,
	score.grade 
FROM
	student
	LEFT JOIN score ON student.st_id = score.st_id;

執行結果：

對於關聯欄位st_id，展示左表所有的記錄。由於右表缺少1003，則1003這行的subject與grade的值被置為null。

---

四、右連線

按照關聯欄位取出兩個表中的記錄，保留右表所有的記錄，以及滿足連線條件的左表記錄，左表中不滿足連線條件的會被置為null。正好與左連線相反。

例如：

SELECT
	student.st_id,
	student.st_name,
	score.subject,
	score.grade 
FROM
	student
	RIGHT JOIN score ON student.st_id = score.st_id;

執行結果：

對於關聯欄位st_id，展示右表所有的記錄。由於左表缺少1005，即執行結果的最後一行的st_id與st_name的值被置為null。

---

五、全連線

按照關聯欄位取出兩個表中的記錄，保留左右兩表中所有的記錄，不滿足連線條件的均被置為null。

當然，oracle可以直接使用full join來完成全連線，而mysql則需要藉助union。

例如：

select student.st_id,student.st_name,score.subject,score.grade 
from student left join score on student.st_id=score.st_id
union
select student.st_id,student.st_name,score.subject,score.grade 
from student right join score on student.st_id=score.st_id;

執行結果：

可以看到，已經取出了兩個表中的所有資料。

---

六、獲取交集以外的部分

按照關聯欄位取出兩個表中的記錄，保留交集以外的資料。去除交集相當於全連線-內連線。

例如：

select student.st_id,student.st_name,score.subject,score.grade 
from student left join score on student.st_id=score.st_id 
where score.st_id is null
union
select student.st_id,student.st_name,score.subject,score.grade 
from student right join score on student.st_id=score.st_id 
where student.st_id is null;

執行結果：

第一條資料屬於student表，第二條資料屬於score表。