目录
那么之前说过了各种连接,其实union本质上还是和自然连接,内连接,外连接不同的,union是对结果的结合,而三类连接能使用的范围更广。那么今天就说说Union的用法以及含义。
每日提醒:本文所用的数据都是我本人自己手动建表的,为了方便各位验证,建表sql语句在《【数据库CS751】数据库的建表与插入》这篇文章中完整呈现。
一、Union的含义
union的含义其实就是离散数学中并集的概念,它的关系代数也是∪的符号,具体参考【数据库CS751】关系代数总结(包括一元关系,二元关系与聚合关系)
所以我们就要求union连接的结果:
相同类型,是同一个含义(同一个东西) :这个很好理解,你既然是并集,那么你要并相同类型的东西,比如:{西瓜,香蕉},{苹果,橘子},都是水果,那当然可以,如果是{西瓜,香蕉},{大海,天空},这两个集合并起来就没有任何实际意义。
二、Union的用法
首先先说,什么情况下能够使用union:
1.列数一样:这个很重要:
例如:选项1如下
select e.Fname,e.Lname from employee as e
where (e.Salary>40000 and e.Dno = 4) or (e.Salary<30000 and e.Dno = 5)
选项2:
select e.Fname from employee as e
where e.Salary<10000 and e.Dno = 1
这两个select的结果是不允许union的,因为所选列数不同
我们用一下union看看会有什么现象:
select e.Fname,e.Lname from employee as e
where (e.Salary>40000 and e.Dno = 4) or (e.Salary<30000 and e.Dno = 5)
union
select e.Fname from employee as e
where e.Salary<10000 and e.Dno = 1
结果是:
可能看不清:
所以,这是基础要求。
2.union与union all
union操作是去重的,union all是展示所有结果:
我们看一个极端的例子:union的两端选取一样的东西:
select e.Fname,e.Lname from employee as e
where (e.Salary>40000 and e.Dno = 4) or (e.Salary<30000 and e.Dno = 5)
union
select e.Fname,e.Lname from employee as e
where (e.Salary>40000 and e.Dno = 4) or (e.Salary<30000 and e.Dno = 5)
union只有两个结果
union all 试一试:
就会将出现重复项。
3.union可以排序么
当然可以:注意order by不要加表名,因为此时的Fname是全局Fname
(select e.Fname,e.Lname from employee as e
where (e.Salary>40000 and e.Dno = 4) or (e.Salary<30000 and e.Dno = 5)
union all
select e.Fname,e.Lname from employee as e
where (e.Salary>40000 and e.Dno = 4) or (e.Salary<30000 and e.Dno = 5))
order by Fname
结果:
4.别名会影响union么?
不会的,我们可以看例子:
(select e.Fname as ttttt,e.Lname from employee as e
where (e.Salary>40000 and e.Dno = 4) or (e.Salary<30000 and e.Dno = 5)
union all
select e.Fname,e.Lname from employee as e
where (e.Salary>40000 and e.Dno = 4) or (e.Salary<30000 and e.Dno = 5))
结果:
那么别名会不会影响排序?
如果是第一个表的列起别名,那就会影响,第二个不影响,一切以第一个为准,就比如:我刚刚将第一个表的Fname重命名为ttttt,那么我再order的时候就要写全局ttttt
例如:
(select e.Fname as ttttt,e.Lname from employee as e
where (e.Salary>40000 and e.Dno = 4) or (e.Salary<30000 and e.Dno = 5)
union all
select e.Fname,e.Lname from employee as e
where (e.Salary>40000 and e.Dno = 4) or (e.Salary<30000 and e.Dno = 5))
order by ttttt
结果:
如果我依然用Fname:
(select e.Fname as ttttt,e.Lname from employee as e
where (e.Salary>40000 and e.Dno = 4) or (e.Salary<30000 and e.Dno = 5)
union all
select e.Fname,e.Lname from employee as e
where (e.Salary>40000 and e.Dno = 4) or (e.Salary<30000 and e.Dno = 5))
order by Fname
报错:
如果是第二个表的变为ttttt
(select e.Fname ,e.Lname from employee as e
where (e.Salary>40000 and e.Dno = 4) or (e.Salary<30000 and e.Dno = 5)
union all
select e.Fname as ttttt,e.Lname from employee as e
where (e.Salary>40000 and e.Dno = 4) or (e.Salary<30000 and e.Dno = 5))
order by Fname
那么是不受影响的:
所以如果你要重命名,一切以第一个表为主!