文章目录

创建组合查询示例：多条where语句和union实现达到相同效果 union规则对组合查询结果排序 - 组合查询也叫并，复合查询。 有多个where子句的select语句可以作为一个组合查询。

创建组合查询

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

先看两个查询

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

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

第二个查询查的是来自fun4all公司的所有顾客

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

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

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

但是除了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';

多条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';

union规则

如果被组合的两个查询的列不完全相同，就会报错，下面代码会报错：

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';

对组合查询结果排序

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;

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