当前位置:首页 > 开发 > 数据库 > 正文

MySQL: Union

发表于: 2013-04-18   作者:DavyJones2010   来源:转载   浏览次数:
摘要: 1. Union     1) Function: Combine the result sets from two query into one single result set. # Single result set 1 select * from stu where stu_score > 40; # Single result set 2 sel

1. Union

    1) Function: Combine the result sets from two query into one single result set.

# Single result set 1
select * from stu where stu_score > 40;

# Single result set 2
select * from stu where stu_score < 35;

# Combine result sets without union
select * from stu where stu_score < 35 || stu_score > 40;

# Combine result sets with union
select * from stu where stu_score > 40 union select * from stu where stu_score < 35;

     2) Compliance of Union

# The column number of the result sets of the two queries should be the same
# But it's recommended that the structure of the two result sets be the same

     3) Usage scenario

# In the ECShop project
# Comments are stored in two tables: 1) comment[For common comment] 2) feedback[For problem argue]
# The structure of the two tables are not that same.
# But the compliance is that the result set of the two queries should be the same.
# But in the message panel we need to see all the two types' comment. So it's best to use union instead of using two SQL statements and then combine the data in java/php/c code.

2. An example for exmplaining the usage of union

#1. Table ta
select * from ta;
+----+-----+
| id | num |
+----+-----+
| a  |   5 |
| b  |  10 |
| c  |  15 |
| d  |  10 |
+----+-----+

#2. Table tb
select * from tb;
+----+-----+
| id | num |
+----+-----+
| b  |   5 |
| c  |  10 |
| d  |  20 |
| e  |  99 |
+----+-----+

#3. Union result of the two tables
select * from ta union select * from tb;
+----+-----+
| id | num |
+----+-----+
| a  |   5 |
| b  |  10 |
| c  |  15 |
| d  |  10 |
| b  |   5 |
| c  |  10 |
| d  |  20 |
| e  |  99 |
+----+-----+

#4. Combine the num whose id are the same
select id, sum(num) from (select * from ta union select * from tb) as temp group by id;
+----+----------+
| id | sum(num) |
+----+----------+
| a  | 5        |
| b  | 15       |
| c  | 25       |
| d  | 30       |
| e  | 99       |
+----+----------+

3. A pitfall when using union

# 1. Table ta
select * from ta;
+----+-----+
| id | num |
+----+-----+
| a  |   5 |
| b  |  10 |
| c  |  15 |
| d  |  10 |
+----+-----+

# 2. Table tb
select * from tb;
+----+-----+
| id | num |
+----+-----+
| b  |   5 |
| c  |  15 |
| d  |  20 |
| e  |  99 |
+----+-----+

# 3. Union result
select * from ta union select * from tb;
+----+-----+
| id | num |
+----+-----+
| a  |   5 |
| b  |  10 |
| c  |  15 |
| d  |  10 |
| b  |   5 |
| d  |  20 |
| e  |  99 |
+----+-----+

# Pay attention to the row whose id = c;
# When ta.id == tb.id && ta.num == tb.num <Every column on current row are the same value>
# Then the union result will combine the two rows together to reduce redundancy;
# We can use key word union all instead of union to suppress auto combination.

select * from ta union all select * from tb;
+----+-----+
| id | num |
+----+-----+
| a  |   5 |
| b  |  10 |
| c  |  15 |
| d  |  10 |
| b  |   5 |
| c  |  15 |
| d  |  20 |
| e  |  99 |
+----+-----+

4. Another pitfall when using union

# 1. Common pitfall
select * from ta order by id desc union all select * from tb order by id desc;
ERROR 1221 : Incorrect usage of UNION and ORDER BY

# 2. Reason
# Because union all don't know how to order the two result set.

# 3. Solution: Add () to separate two result set to eliminate ambiguity.
# But that wouldn't affect the order of the final result. Ordered result set a union order result b, the result wouldn't be ordered even in each sub-result-set
# It's recommanded using order by or limit at the end of the final result set.
## Not recommanded:
(select * from ta order by id desc) union all (select * from tb order by id desc);
## Recommanded:
(select * from ta) union all (select * from tb) order by id desc;

# Comment:
(select * from ta order by id desc limit 4) union all (select * from tb order by id desc limit 5);
# It works!

# Reason:
# It's pointless union two ordered result set who is ordered wihout any purpose. Then it would be optimized by compiler saying order is useless in each sub-result-set.
# It's better practice union two ordered result set who is ordered with specific purpose. Such as get the largest three id or smallest three id. It wouldn't be optimized by compiler.

 

Comments:

    1) The column name of the SQL result are not forced to be the same. But the type of the column should be the same. And the result of the union operation, the name of the column is set as the left table's column name.

    2) In the previous example, if table ta's id column is primary key for ta table and if table tb's id column is primary key for tb table. When we union the two table there would be no error occurs. Because the result is just a result set (virtual table) not a real table whose id mustn't be duplicated.

MySQL: Union

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
union的作用很简单用来合并两条sql的结果集 语法: SQL1 UNION SQL2 现有一张价格表 要求:求出价格
感觉工作之后一直在用框架,数据库的一些基本的东西都忘记了,这次借着这个系列的博客回顾一下旧知
OR、in和union all 查询效率到底哪个快。 网上很多的声音都是说union all 快于 or、in,因为or、in
OR、in和union all 查询效率到底哪个快。 网上很多的声音都是说union all 快于 or、in,因为or、in
今天有个项目出现了问题 问题原因是union和order by 的问题。关于这个问题的解决方案可以猛击下面的
  内连接查询   内连接是把2张表符合条件的行左右拼接在一起,select xxx from table1 inner jo
union 和 union all 都是将两个结果集合合为一个 但是他们使用和效率上来说都有所不同 union 进行连
union 和 union all 都是将两个结果集合合为一个 但是他们使用和效率上来说都有所不同 union 进行连
Union和Union all 的区别 在数据库中,union和union all关键字都是将两个结果集合并为一个,但这两
Union和Union all 的区别 在数据库中,union和union all关键字都是将两个结果集合并为一个,但这两
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号