SQL （十三）组合查询(union操作符把多条select语句组合为一个结果集)

- 日理万妓 2023-02-21 15:27 8阅读 0赞

### 文章目录 ###

*  创建组合查询
 *   *  示例：多条where语句和union实现达到相同效果
 *  union规则
 *  对组合查询结果排序

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NjA3ODk0_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][20200701130504125.png]\- 组合查询也叫并，复合查询。  
有多个where子句的select语句可以作为一个组合查询。

# 创建组合查询 #

![在这里插入图片描述][20200701124342105.png]  
![在这里插入图片描述][20200701124457350.png]

## 示例：多条where语句和union实现达到相同效果 ##

先看两个查询

第一个查询出Illinois、Indiana和Michigan三个州（美国）的所有顾客的三项信息

select cust_name, cust_contact, cust_email
    from Customers
    where cust_state in ('IL', 'IN', 'MI');

![在这里插入图片描述][20200701124824555.png]  
第二个查询查的是来自fun4all公司的所有顾客

select cust_name, cust_contact, cust_email
    from Customers
    where cust_name = 'Fun4All';

![在这里插入图片描述][20200701124916465.png]

我想把两个查询结果组合起来，那应该得到4项结果，可以用where子句的or子句实现

select cust_name, cust_contact, cust_email
    from Customers
    where cust_name = 'Fun4All'
    or cust_state in ('IL', 'IN', 'MI');

![在这里插入图片描述][20200701125147901.png]

但是除了or子句外，还有一个办法，即用union关键字

select cust_name, cust_contact, cust_email
    from Customers
    where cust_state in ('IL', 'IN', 'MI') --第一个select语句不加分号
    union
    select cust_name, cust_contact, cust_email
    from Customers
    where cust_name = 'Fun4All';

![在这里插入图片描述][20200701125248963.png]  
![在这里插入图片描述][20200701125321981.png]![在这里插入图片描述][20200701125341621.png]  
![在这里插入图片描述][20200701125438968.png]

*  多条where子句和union，哪个性能更好，需要自己测试一下才知道

可以看到，union自动去除了重复的行（默认行为），如果你不想让他删除重复行而是返回所有行，那就用union all

select cust_name, cust_contact, cust_email
    from Customers
    where cust_state in ('IL', 'IN', 'MI')
    union all
    select cust_name, cust_contact, cust_email
    from Customers
    where cust_name = 'Fun4All';

![在这里插入图片描述][2020070112591872.png]

![在这里插入图片描述][20200701130020223.png]

# union规则 #

![在这里插入图片描述][20200701125612112.png]如果被组合的两个查询的列不完全相同，就会报错，下面代码会报错：

select cust_name, cust_contact, cust_email
    from Customers
    where cust_state in ('IL', 'IN', 'MI')
    union
    select cust_name, cust_contact, cust_email, cust_id
    from Customers
    where cust_name = 'Fun4All';

![在这里插入图片描述][20200701125715211.png]

# 对组合查询结果排序 #

![在这里插入图片描述][20200701130148640.png]

select cust_name, cust_contact, cust_email
    from Customers
    where cust_state in ('IL', 'IN', 'MI')
    union
    select cust_name, cust_contact, cust_email
    from Customers
    where cust_name = 'Fun4All'
    order by cust_name,cust_contact;

![在这里插入图片描述][20200701130238570.png]  
![在这里插入图片描述][20200701130331398.png]

我试了试，mysql根本不支持except, intersect, minus

![在这里插入图片描述][20200701130437805.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NjA3ODk0_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200701124224541.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NjA3ODk0,size_16,color_FFFFFF,t_70
[20200701130504125.png]: https://img-blog.csdnimg.cn/20200701130504125.png
[20200701124342105.png]: https://img-blog.csdnimg.cn/20200701124342105.png
[20200701124457350.png]: https://img-blog.csdnimg.cn/20200701124457350.png
[20200701124824555.png]: https://img-blog.csdnimg.cn/20200701124824555.png
[20200701124916465.png]: https://img-blog.csdnimg.cn/20200701124916465.png
[20200701125147901.png]: https://img-blog.csdnimg.cn/20200701125147901.png
[20200701125248963.png]: https://img-blog.csdnimg.cn/20200701125248963.png
[20200701125321981.png]: https://img-blog.csdnimg.cn/20200701125321981.png
[20200701125341621.png]: https://img-blog.csdnimg.cn/20200701125341621.png
[20200701125438968.png]: https://img-blog.csdnimg.cn/20200701125438968.png
[2020070112591872.png]: https://img-blog.csdnimg.cn/2020070112591872.png
[20200701130020223.png]: https://img-blog.csdnimg.cn/20200701130020223.png
[20200701125612112.png]: https://img-blog.csdnimg.cn/20200701125612112.png
[20200701125715211.png]: https://img-blog.csdnimg.cn/20200701125715211.png
[20200701130148640.png]: https://img-blog.csdnimg.cn/20200701130148640.png
[20200701130238570.png]: https://img-blog.csdnimg.cn/20200701130238570.png
[20200701130331398.png]: https://img-blog.csdnimg.cn/20200701130331398.png
[20200701130437805.png]: https://img-blog.csdnimg.cn/20200701130437805.png