Union和Union All的区别

时间：2022-06-29 15:47:52
使用mysql数据库中 mysql库中的 表 help_category


1>UNION 自动去除 重复数据，UNION  ALL 不会

SELECT * FROM help_category t WHERE t.help_category_id<=7
UNION
SELECT * FROM help_category t WHERE t.help_category_id>=5 AND t.help_category_id<=10 ;

结果：
   
<
help_category_id name parent_category_id
1 Geographic 0
2 Polygon properties 33
3 WKT 33
4 Numeric Functions 37
5 Plugins 34
6 MBR 33
7 Control flow functions 37
8 Transactions 34
9 Help Metadata 34
10 Account Management 34



 
SELECT * FROM help_category t WHERE t.help_category_id<=7
UNION  ALL
SELECT * FROM help_category t WHERE t.help_category_id>=5 AND t.help_category_id<=10 ;

结果：

<
help_category_id name parent_category_id url
1 Geographic 0  
2 Polygon properties 33  
3 WKT 33  
4 Numeric Functions 37  
5 Plugins 34  
6 MBR 33  
7 Control flow functions 37  
5 Plugins 34  
6 MBR 33  
7 Control flow functions 37  
8 Transactions 34  
9 Help Metadata 34  
10 Account Management 34  



2>顺序问题
SELECT * FROM help_category t WHERE t.help_category_id>=5 AND t.help_category_id<=10  
UNION
SELECT * FROM help_category t WHERE t.help_category_id<=7;
<
help_category_id name parent_category_id url
5 Plugins 34  
6 MBR 33  
7 Control flow functions 37  
8 Transactions 34  
9 Help Metadata 34  
10 Account Management 34  
1 Geographic 0  
2 Polygon properties 33  
3 WKT 33  
4 Numeric Functions 37  


调换顺序后，结果无序

SELECT * FROM help_category t WHERE t.help_category_id>=5 AND t.help_category_id<=10  
UNION ALL
SELECT * FROM help_category t WHERE t.help_category_id<=7;
<
help_category_id name parent_category_id url
5 Plugins 34  
6 MBR 33  
7 Control flow functions 37  
8 Transactions 34  
9 Help Metadata 34  
10 Account Management 34  
1 Geographic 0  
2 Polygon properties 33  
3 WKT 33  
4 Numeric Functions 37  
5 Plugins 34  
6 MBR 33  
7 Control flow functions 37  


同样 ，调换顺序后结果无序

所以为了保证数据的有序，需要在 最后那个sql 中加上order by

SELECT * FROM help_category t WHERE t.help_category_id>=5 AND t.help_category_id<=10  
UNION ALL
SELECT * FROM help_category t WHERE t.help_category_id<=7 ORDER BY help_category_id;
<
help_category_id name parent_category_id url
1 Geographic 0  
2 Polygon properties 33  
3 WKT 33  
4 Numeric Functions 37  
5 Plugins 34  
5 Plugins 34  
6 MBR 33  
6 MBR 33  
7 Control flow functions 37  
7 Control flow functions 37  
8 Transactions 34  
9 Help Metadata 34  
10 Account Management 34  


SELECT * FROM help_category t WHERE t.help_category_id>=5 AND t.help_category_id<=10  
UNION
SELECT * FROM help_category t WHERE t.help_category_id<=7 ORDER BY help_category_id;
<
help_category_id name parent_category_id url
1 Geographic 0  
2 Polygon properties 33  
3 WKT 33  
4 Numeric Functions 37  
5 Plugins 34  
6 MBR 33  
7 Control flow functions 37  
8 Transactions 34  
9 Help Metadata 34  
10 Account Management 34  


Union，对两个结果集进行并集操作，不包括重复行

Union All，对两个结果集进行并集操作，包括重复行



最后把所有的sql脚本保留一下

SELECT * FROM help_category t WHERE t.help_category_id<=7 
UNION 
SELECT * FROM help_category t WHERE t.help_category_id>=5 AND t.help_category_id<=10 ;

SELECT * FROM help_category t WHERE t.help_category_id<=7 
UNION ALL
SELECT * FROM help_category t WHERE t.help_category_id>=5 AND t.help_category_id<=10 ;

--=================================================================================
SELECT * FROM help_category t WHERE t.help_category_id>=5 AND t.help_category_id<=10  
UNION 
SELECT * FROM help_category t WHERE t.help_category_id<=7 ORDER BY help_category_id;

SELECT * FROM help_category t WHERE t.help_category_id<=7 
UNION 
SELECT * FROM help_category t WHERE t.help_category_id>=5 AND t.help_category_id<=10 ORDER BY help_category_id;

--=================================================================================
SELECT * FROM help_category t WHERE t.help_category_id>=5 AND t.help_category_id<=10  
UNION ALL
SELECT * FROM help_category t WHERE t.help_category_id<=7 ORDER BY help_category_id;

SELECT * FROM help_category t WHERE t.help_category_id<=7 
UNION ALL
SELECT * FROM help_category t WHERE t.help_category_id>=5 AND t.help_category_id<=10 ORDER BY help_category_id;


标签：

相关文章