数据库左连接、右连接、内连接、全连接
数据库中各种连接方式的效果示意图如下所示:
下面通过案例来理解一下各种连接的使用场景。为了直观的观察上述不同连接操作的结果,首先准备两张表:
-- sql_test.a definition
CREATE TABLE `a` (
`id` int NOT NULL AUTO_INCREMENT,
`value` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- sql_test.a definition
CREATE TABLE `b` (
`id` int NOT NULL AUTO_INCREMENT,
`value` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
然后分别在a表和b表中添加一些数据,如下所示:
mysql> select * from a;
+----+----------+
| id | value |
+----+----------+
| 1 | Kobe |
| 2 | James |
| 3 | Forlogen |
| 4 | Bill |
| 5 | Amy |
| 6 | Coco |
+----+----------+
6 rows in set (0.00 sec)
mysql> select * from b;
+----+----------+
| id | value |
+----+----------+
| 1 | Kobe |
| 2 | James |
| 3 | Forlogen |
| 7 | Green |
| 8 | Pope |
| 9 | Adam |
+----+----------+
6 rows in set (0.00 sec)
内连接(inner join)
内连接相当于找两个表的交集,执行如下语句:
mysql> select * from a inner join b on a.id=b.id;
+----+----------+----+----------+
| id | value | id | value |
+----+----------+----+----------+
| 1 | Kobe | 1 | Kobe |
| 2 | James | 2 | James |
| 3 | Forlogen | 3 | Forlogen |
+----+----------+----+----------+
3 rows in set (0.00 sec)
左连接(left join)
左连接是左边表的所有数据都能被查询出来,右边表只能查询出和左边表公布的数据,其他的数据显示为null。
mysql> select * from a left join b on a.id=b.id;
+----+----------+------+----------+
| id | value | id | value |
+----+----------+------+----------+
| 1 | Kobe | 1 | Kobe |
| 2 | James | 2 | James |
| 3 | Forlogen | 3 | Forlogen |
| 4 | Bill | NULL | NULL |
| 5 | Amy | NULL | NULL |
| 6 | Coco | NULL | NULL |
+----+----------+------+----------+
6 rows in set (0.00 sec)
右连接(right join)
右连接是右边表的所有数据都能被查询出来,左边表只能查询出和右边表公布的数据,其他的数据显示为null。
mysql> select * from a right join b on a.id=b.id;
+------+----------+----+----------+
| id | value | id | value |
+------+----------+----+----------+
| 1 | Kobe | 1 | Kobe |
| 2 | James | 2 | James |
| 3 | Forlogen | 3 | Forlogen |
| NULL | NULL | 7 | Green |
| NULL | NULL | 8 | Pope |
| NULL | NULL | 9 | Adam |
+------+----------+----+----------+
6 rows in set (0.00 sec)
外连接/全连接(outer join)
外连接(全连接)查询出两个表不重复的所有数据。
mysql不支持外连接。
同时使用左连接和右连接实现全连接的效果,使用UNION来保存不重复的数据。
mysql> select a.id a_id,a.value a_value,b.id b_id,b.value b_value from a left join b on a.id=b.id
-> union
-> select a.id a_id,a.value a_value,b.id b_id,b.value b_value from a right join b on a.id=b.id;
+------+----------+------+----------+
| a_id | a_value | b_id | b_value |
+------+----------+------+----------+
| 1 | Kobe | 1 | Kobe |
| 2 | James | 2 | James |
| 3 | Forlogen | 3 | Forlogen |
| 4 | Bill | NULL | NULL |
| 5 | Amy | NULL | NULL |
| 6 | Coco | NULL | NULL |
| NULL | NULL | 7 | Green |
| NULL | NULL | 8 | Pope |
| NULL | NULL | 9 | Adam |
+------+----------+------+----------+
9 rows in set (0.00 sec)
上面的查询结果是分开显示的,如果想要在一起显示,可以使用select * from a union select * from b实现。
mysql> select * from a union select * from b;
+----+----------+
| id | value |
+----+----------+
| 1 | Kobe |
| 2 | James |
| 3 | Forlogen |
| 4 | Bill |
| 5 | Amy |
| 6 | Coco |
| 7 | Green |
| 8 | Pope |
| 9 | Adam |
+----+----------+
9 rows in set (0.00 sec)
左连接不包含内连接(left join excluding inner join)
查询得到的是左表独有的数据。
mysql> select a.id a_id,a.value a_value,b.id b_id,b.value b_value from a
-> left join b on a.id=b.id where b.id is null;
+------+---------+------+---------+
| a_id | a_value | b_id | b_value |
+------+---------+------+---------+
| 4 | Bill | NULL | NULL |
| 5 | Amy | NULL | NULL |
| 6 | Coco | NULL | NULL |
+------+---------+------+---------+
3 rows in set (0.00 sec)
右连接不包含内连接(right join excluding inner join)
查询得到的是右表独有的数据。
mysql> select a.id a_id,a.value a_value,b.id b_id,b.value b_value from a
-> right join b on a.id=b.id where a.id is null;
+------+---------+------+---------+
| a_id | a_value | b_id | b_value |
+------+---------+------+---------+
| NULL | NULL | 7 | Green |
| NULL | NULL | 8 | Pope |
| NULL | NULL | 9 | Adam |
+------+---------+------+---------+
3 rows in set (0.00 sec)
总结
对于Mysql来说,它支持内连接、左连接和右连接,其他形式的连接实现,需要使用where来设置一定的限制条件来实现。