【MySQL】各种连接（左连接，右连接，内连接，全连接等等）

示意图

---

1、内连接（INNER JOIN）

代码如下：

SELECT  A.PK AS A_PK,A.Value AS A_Value,B.PK AS B_PK,B.Value AS B_Value
FROM table_a A
INNER JOIN table_b B
ON A.PK = B.PK;

查询结果：

---

2、左连接（LEFT JOIN）

代码如下：

SELECT  A.PK AS A_PK,A.Value AS A_Value,B.PK AS B_PK,B.Value AS B_Value
FROM table_a A
LEFT JOIN  table_b B
ON A.PK = B.PK;

查询结果：

---

3、右连接（RIGHT JOIN）

代码如下：

SELECT  A.PK AS A_PK,A.Value AS A_Value,B.PK AS B_PK,B.Value AS B_Value
FROM table_a A
RIGHT JOIN   table_b B
ON A.PK = B.PK;

查询结果：

---

4、全连接（FULL JOIN）

代码如下：

SELECT  A.PK AS A_PK,A.Value AS A_Value,B.PK AS B_PK,B.Value AS B_Value
FROM table_a A
FULL  JOIN table_b B
ON A.PK = B.PK;

上面代码在mysql执行是报错的，因为mysql不支持全连接，只能用以下代码实现效果，含义是左连接+右连接+去重=全。

所谓的去重，实际上就是使用UNION，取并集：

SELECT  A.PK AS A_PK,A.Value AS A_Value,B.PK AS B_PK,B.Value AS B_Value
FROM table_a A
LEFT JOIN  table_b B
ON A.PK = B.PK
 UNION
SELECT  A.PK AS A_PK,A.Value AS A_Value,B.PK AS B_PK,B.Value AS B_Value
FROM table_a A
RIGHT JOIN  table_b B
ON A.PK = B.PK;

查询结果：

---

5、左连接不包含内连接

代码如下：

SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK
WHERE B.PK IS NULL

查询结果：

---

6、右连接不包含内连接

代码如下：

SELECT A.PK AS A_PK, A.Value AS A_Value, B.PK AS B_PK,
B.Value AS B_Value
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL

查询结果：

---

7、外连接不包含内连接

代码如下：

SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL
OR B.PK IS NULL

由于mysql不支持full join，只能通过下面代码模拟实现：

SELECT  A.PK AS A_PK, A.Value AS A_Value, B.PK AS B_PK,
B.Value AS B_Value
 FROM table_a A
 LEFT JOIN table_b B
ON A.PK = B.PK
 WHERE B.PK IS NULL
UNION ALL
 SELECT *
 FROM table_a A
 RIGHT JOIN table_b B
 ON A.PK = B.PK
  WHERE A.PK IS NULL;

查询结果：