The usage of Union & Union All-优快云博客

本文通过具体示例介绍了SQL中UNION和UNION ALL的区别及使用方法。UNION用于合并两个查询结果集，去除重复记录；UNION ALL同样用于合并结果集，但保留所有记录包括重复项。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、UNION 中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出)
2、列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含转换的类型
(例如不同的数值类型或不同的日期类型)

The following script gives some examples.

[@more@]

SQL> COLUMN cust_name FORMAT A20
SQL> COLUMN cust_contact FORMAT A20
SQL> COLUMN cust_email FORMAT A25
SQL> SELECT cust_name,cust_contact,cust_email FROM customers;

CUST_NAME CUST_CONTACT CUST_EMAIL

-------------------- -------------------- --------------------

VillageToys johnSmith sales@villagetoys.com

Fun4All Jim Jones jjones@fun4all.com
The Toy Store Kim Howard
Note:3records
SQL> SELECT cust_name,cust_contact,cust_email FROM customers
2 WHERE cust_name = 'Fun4All';

CUST_NAME CUST_CONTACT CUST_EMAIL

-------------------- -------------------- -------------------------Fun4All Jim Jones jjones@fun4all.com
Fun4All Denise L. Stephens dstephens@fun4all.com
Note:2 records
SQL> SELECT cust_name,cust_contact,cust_email FROM customers
2 WHERE cust_state IN ('IL','IN','MI')
3 UNION
4 SELECT cust_name,cust_contact,cust_email FROM customers
5 WHERE cust_name = 'Fun4ALL'
6 ORDER BY cust_name,cust_contact;

CUST_NAME CUST_CONTACT CUST_EMAIL

-------------------- -------------------- ------------------------
Fun4All Jim Jones jjones@fun4all.com
Fun4All DeniseL.Stephens dstephens@fun4all.com
Toy Store Kim Howard
Village Toys John Smith sales@villagetoys.com
Note: 4 records ,it doesn't include the repeated records
SQL> SELECT cust_name,cust_contact,cust_email FROM customers
2 WHERE cust_state IN ('IL','IN','MI')
3 UNION ALL
4 SELECT cust_name,cust_contact,cust_email FROM customers
5 WHERE cust_name = 'Fun4ALL'
6 ORDER BY cust_name,cust_contact;

CUST_NAME CUST_CONTACT CUST_EMAIL

-------------------- -------------------- -------------------------
Fun4All Jim Jones jjones@fun4all.com
Fun4All Denise L. Stephens dstephens@fun4all.com
Fun4All Jim Jones jjones@fun4all.com
Toy Store Kim Howard
Village Toys John Smith sales@villagetoys.com
Note: 5 records, it include the repeated records

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10484922/viewspace-1002178/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10484922/viewspace-1002178/