联合查询技巧-优快云博客

本文链接：https://blog.youkuaiyun.com/HanghangzZ/article/details/109552914

联合查询

UNION

SELECT 字段名称,... FROM tbl_name1
UNION
SELECT 字段名称,... FROM tbl_name2;

在联合查询时会去掉表中的重复记录

UNION ALL

SELECT 字段名称,... FROM tbl_name1
UNION ALL
SELECT 字段名称,... FROM tbl_name2;

在联合查询时只是简单的合并两个表中的记录

测试

以下是测试表的内容

SELECT * FROM user4;
+----+----------+
| id | username |
+----+----------+
|  1 | king     |
|  2 | queen    |
|  3 | imooc    |
|  4 | lily     |
|  5 | rose     |
|  6 | john     |
|  7 | 测试用户 |
|  8 | a        |
|  9 | b        |
| 10 | c        |
| 11 | d        |
| 12 | e        |
| 13 | f        |
| 14 | g        |
+----+----------+

mysql> SELECT * FROM user5;
+----+----------+
| id | username |
+----+----------+
|  1 | king     |
|  2 | queen    |
|  3 | imooc    |
|  4 | lily     |
|  5 | rose     |
|  6 | john     |
|  7 | 测试用户 |
|  8 | a        |
|  9 | b        |
| 10 | c        |
| 11 | d        |
| 12 | e        |
| 13 | f        |
| 14 | g        |
| 15 | tiancai  |
| 16 | tiancai  |
+----+----------+

测试UNION

-- 将user4和user5的数据合并到一起
SELECT * FROM user4
UNION 
SELECT * FROM user5;

+----+----------+
| id | username |
+----+----------+
|  1 | king     |
|  2 | queen    |
|  3 | imooc    |
|  4 | lily     |
|  5 | rose     |
|  6 | john     |
|  7 | 测试用户 |
|  8 | a        |
|  9 | b        |
| 10 | c        |
| 11 | d        |
| 12 | e        |
| 13 | f        |
| 14 | g        |
| 15 | tiancai  |
| 16 | tiancai  |
+----+----------+

我们可以很明显的发现这是去除掉两个表中重复的记录，再将两个表合并

测试UNION ALL

-- 将user4和user5的数据合并到一起
SELECT * FROM user4
UNION ALL
SELECT * FROM user5;

+----+----------+
| id | username |
+----+----------+
|  1 | king     |
|  2 | queen    |
|  3 | imooc    |
|  4 | lily     |
|  5 | rose     |
|  6 | john     |
|  7 | 测试用户 |
|  8 | a        |
|  9 | b        |
| 10 | c        |
| 11 | d        |
| 12 | e        |
| 13 | f        |
| 14 | g        |
|  1 | king     |
|  2 | queen    |
|  3 | imooc    |
|  4 | lily     |
|  5 | rose     |
|  6 | john     |
|  7 | 测试用户 |
|  8 | a        |
|  9 | b        |
| 10 | c        |
| 11 | d        |
| 12 | e        |
| 13 | f        |
| 14 | g        |
| 15 | tiancai  |
| 16 | tiancai  |
+----+----------+

而UNION ALL就是简单的将两个表中所有的内容合并到一起。