union和union all的区别

建立表脚本：
CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `sex` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3

student表测试数据：
    id  username      sex  
------  ---------  --------
     1  xiaoming          1
     2  xiaohua           0
     3  hanmeimei         0
     4  allen             1
     5  penguin           1
     

CREATE TABLE `student2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `sex` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3

student2表测试数据：
    id  username      sex  
------  ---------  --------
     1  xiaoming          1
     2  xiaohua           0
     3  hanmeimei         0
     4  allen             1
     5  justin            1
     

1、union看到结果中去重和排序结果

SELECT * FROM student 
UNION
SELECT * FROM student2 

查询返回数据视图
    id  username      sex  
------  ---------  --------
     1  xiaoming          1
     2  xiaohua           0
     3  hanmeimei         0
     4  allen             1
     5  penguin           1
     5  justin            1
     

2、union all 结果中的结果合并
SELECT * FROM student 
UNION ALL
SELECT * FROM student2 

查询返回数据视图
    id  username      sex  
------  ---------  --------
     1  xiaoming          1
     2  xiaohua           0
     3  hanmeimei         0
     4  allen             1
     5  penguin           1
     1  xiaoming          1
     2  xiaohua           0
     3  hanmeimei         0
     4  allen             1
     5  justin            1