mysql中JOIN大汇总

SQL的join图无外乎下面7种

7种JOIN--图片来源于网络

下面来对这7种一一详解

首先做些准备工作,创建两张表A表和B表

create table A(
id int primary key auto_increment,
key_A int,
name varchar(20));

create table B(
id int primary key auto_increment,
key_B int,
name varchar(20));

表创建好之后，随便插入几条数据

---

• A独有 + AB共有

SQL语句为

mysql> select * from A left join B on A.key_A = B.key_B;
+----+-------+--------+------+-------+--------+
| id | key_A | name   | id   | key_B | name   |
+----+-------+--------+------+-------+--------+
|  3 |     3 | hello3 |    1 |     3 | world1 |
|  4 |     4 | hello4 |    2 |     4 | world2 |
|  5 |     5 | hello5 |    3 |     5 | world3 |
|  1 |     1 | hello1 | NULL |  NULL | NULL   |
|  2 |     2 | hello2 | NULL |  NULL | NULL   |
+----+-------+--------+------+-------+--------+

这种join称为左连接（也称为左外连接），左连接就是左边的表所有数据都保留，没有关联上的用NULL填充

---

• AB共有

SQL语句为：

 mysql> select * from A join B on A.key_A = B.key_B;
 +----+-------+--------+----+-------+--------+
| id | key_A | name   | id | key_B | name   |
+----+-------+--------+----+-------+--------+
|  3 |     3 | hello3 |  1 |     3 | world1 |
|  4 |     4 | hello4 |  2 |     4 | world2 |
|  5 |     5 | hello5 |  3 |     5 | world3 |
+----+-------+--------+----+-------+--------+

这种join称为内连接，等价于：

select * from A inner join B on A.key_A = B.key_B;
select * from A, B where A.key_A = B.key_B;

---

• B独有 + AB共有

SQL语句为：

mysql> select * from A right join B on A.key_A = B.key_B;
+------+-------+--------+----+-------+--------+
| id   | key_A | name   | id | key_B | name   |
+------+-------+--------+----+-------+--------+
|    3 |     3 | hello3 |  1 |     3 | world1 |
|    4 |     4 | hello4 |  2 |     4 | world2 |
|    5 |     5 | hello5 |  3 |     5 | world3 |
| NULL |  NULL | NULL   |  4 |     6 | world4 |
| NULL |  NULL | NULL   |  5 |     7 | world5 |
+------+-------+--------+----+-------+--------+

这种join称为右连接（也称为右外链接），和左连接类似，右边的表所有数据都保留，没有关联上的，用NULL填充

• A独有

---

SQL语句为：

mysql> select * from A left join B on A.key_A = B.key_B where B.key_B is null;
+----+-------+--------+------+-------+------+
| id | key_A | name   | id   | key_B | name |
+----+-------+--------+------+-------+------+
|  1 |     1 | hello1 | NULL |  NULL | NULL |
|  2 |     2 | hello2 | NULL |  NULL | NULL |
+----+-------+--------+------+-------+------+

相当于在左连接的基础上，将AB共有的那边去掉。A独有，肯定不能有B，故加上条件B.key_B is null

---

• B独有

SQL语句为：

select * from A right join B on A.key_A = B.key_B where A.key_A is null;
+------+-------+------+----+-------+--------+
| id   | key_A | name | id | key_B | name   |
+------+-------+------+----+-------+--------+
| NULL |  NULL | NULL |  4 |     6 | world4 |
| NULL |  NULL | NULL |  5 |     7 | world5 |
+------+-------+------+----+-------+--------+

和A独有类似。

---

• A和B全集

SQL语句为：

mysql> select * from A left join B on A.key_A = B.key_B 
     > union 
     > select * from A right join B on A.key_A = B.key_B where A.key_A is null;
+------+-------+--------+------+-------+--------+
| id   | key_A | name   | id   | key_B | name   |
+------+-------+--------+------+-------+--------+
|    3 |     3 | hello3 |    1 |     3 | world1 |
|    4 |     4 | hello4 |    2 |     4 | world2 |
|    5 |     5 | hello5 |    3 |     5 | world3 |
|    1 |     1 | hello1 | NULL |  NULL | NULL   |
|    2 |     2 | hello2 | NULL |  NULL | NULL   |
| NULL |  NULL | NULL   |    4 |     6 | world4 |
| NULL |  NULL | NULL   |    5 |     7 | world5 |
+------+-------+--------+------+-------+--------+

思路是A所有加B独有

---

• A独有 + B独有

SQL语句是：

mysql> select * from A left join B on A.key_A = B.key_B where B.key_B is null
     > union
     > select * from A right join B on A.key_A = B.key_B where A.key_A is null; 
+------+-------+--------+------+-------+--------+
| id   | key_A | name   | id   | key_B | name   |
+------+-------+--------+------+-------+--------+
|    1 |     1 | hello1 | NULL |  NULL | NULL   |
|    2 |     2 | hello2 | NULL |  NULL | NULL   |
| NULL |  NULL | NULL   |    4 |     6 | world4 |
| NULL |  NULL | NULL   |    5 |     7 | world5 |
+------+-------+--------+------+-------+--------+

这个思路就是A独有加上B独有。
如有不对的地方，欢迎指出