MySQL的各种join

先列出两个表TABLE_A和TABLE_B

本人参考于：mazhuang.org/2017/09/11/…

mysql> SELECT * FROM Table_A ORDER BY PK ASC;
+----+---------+
| PK | Value   |
+----+---------+
|  1 | both ab |
|  2 | only a  |
+----+---------+
2 rows in set (0.00 sec)

mysql> SELECT * from Table_B ORDER BY PK ASC;
+----+---------+
| PK | Value   |
+----+---------+
|  1 | both ab |
|  3 | only b  |
+----+---------+
2 rows in set (0.00 sec)
注意PK=1两表都有，PK=2仅A表有，PK=3仅B表有

1. inner join 内连接， 返回的时A，B表的交集。也可以省略inner然后直接用join。

SELECT A.PK AS A_PK, B.PK AS B_PK,
       A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
INNER JOIN Table_B B
ON A.PK = B.PK;
结果如下：
+------+------+---------+---------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+---------+---------+
|    1 |    1 | both ab | both ab |
+------+------+---------+---------+
1 row in set (0.00 sec)

2. left join左连接，以左边的表为主表。

1）左表有但右表没有则左表部分正常显示，右表部分显示null；

2）左表没有但右表有则不显示

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

#查询结果:A,B表都有则两边都正常显示;A表有B表无,则B表的字段都是null;A表无B表有(PK=3)的记录不显示
+------+------+---------+---------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+---------+---------+
|    1 |    1 | both ab | both ba |
|    2 | NULL | only a  | NULL    |
+------+------+---------+---------+
2 rows in set (0.00 sec)

3. right join右连接，以右边的表为主表。

SELECT A.PK AS A_PK, B.PK AS B_PK,
       A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK;
#结果
+------+------+---------+---------+
| A_PK | B_PK | A_Value | B_Value |
+------+------+---------+---------+
|    1 |    1 | both ab | both ba |
| NULL |    3 | NULL    | only b  |
+------+------+---------+---------+
2 rows in set (0.00 sec)

4. full outer join(full join)全联接(外连接)，返回左右表的所有记录。

其实就是(left join) union all (right join)

SELECT A.PK AS A_PK, B.PK AS B_PK,
       A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK;
#结果
+------+---------+------+---------+
| PK   | Value   | PK   | Value   |
+------+---------+------+---------+
|    1 | both ab |    1 | both ba |
|    2 | only a  | NULL | NULL    |
| NULL | NULL    |    3 | only b  |
+------+---------+------+---------+
3 rows in set (0.00 sec)

5. union all 和union

SELECT column,... FROM table1 
UNION [ALL]
SELECT column,... FROM table2
...
1. 在多个 SELECT 语句中，对应的列应该具有相同的字段属性，且第一个 SELECT 语句中被使用的字段名称也被用于结果的字段名称。
2. 当使用UNION时,MySQL会把结果集中重复的记录删掉;而UNION ALL,则会把所有的记录返回,效率高于 UNION。
3. 重复记录是指查询中各个字段完全重复的记录，若id一样但其他字段不一样算作不同记录。
4. 第一个 SELECT 语句中被使用的字段名称也被用于结果的字段名称
5. 各 SELECT 语句字段名称可以不同，但字段属性必须一致。