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独有。
如有不对的地方，欢迎指出
网友评论