GaussDB(DWS) UNION操作符的使用

在SQL中，UNION是一个非常强大的功能，UNION操作符用于合并两个或多个SELECT语句的结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

UNION在合并两个或多个表时会执行去重操作，而UNION ALL则直接将两个或者多个结果集合并，不执行去重。另外，执行去重会消耗大量的时间，因此，在一些实际应用场景中，如果通过业务逻辑已确认了两个集合不存在重复数据时，可直接用UNION ALL替代UNION，以便提升性能。

点击放大

语法格式

    
         SELECT column,... FROM table1 UNION [ALL]SELECT column,... FROM table2

示例

创建学生信息表student（ID、姓名、性别、学校）。

      
           SET current_schema=public;
DROP TABLE IF EXISTS student;
CREATE table student( 
sId VARCHAR(10) NOT NULL,
sname VARCHAR(10) NOT NULL,
sgender VARCHAR(10) NOT NULL,
sschool VARCHAR(10) NOT NULL);

给表student插入数据。

      
           INSERT INTO student VALUES('s01' , 'ZhaoLei' , 'male', 'NENU'); 
INSERT INTO student VALUES('s02' , 'QianDian' , 'male', 'SJTU'); 
INSERT INTO student VALUES('s03' , 'SunFenng' , 'male', 'Tongji'); 
INSERT INTO student VALUES('s04' , 'LIYun' , 'male', 'CCOM'); 
INSERT INTO student VALUES('s05' , 'ZhouMei' , 'female', 'FuDan'); 
INSERT INTO student VALUES('s06' , 'WuLan' , 'female', 'WHU'); 
INSERT INTO student VALUES('s07' , 'ZhengZhu' , 'female', 'NWAFU'); 
INSERT INTO student VALUES('s08' , 'ZhangShan' , 'female', 'Tongji');

查看表student。
1

SELECT * FROM student;
回显如下：

创建教师信息表teacher（ID、姓名、性别、学校）。

      
           DROP TABLE IF EXISTS teacher;
CREATE table teacher( 
tid VARCHAR(10) NOT NULL,
tname VARCHAR(10) NOT NULL,
tgender VARCHAR(10) NOT NULL,
tschool VARCHAR(10) NOT NULL);

给表teacher插入数据。

      
           INSERT INTO teacher VALUES('t01' , 'ZhangLei', 'male', 'FuDan'); 
INSERT INTO teacher VALUES('t02' , 'LiLiang', 'male', 'WHU'); 
INSERT INTO teacher VALUES('t03' , 'WangGang', 'male', 'Tongji');

查询表teacher。
1

SELECT * FROM teacher;

使用UNION（合并且去重）获取学生和教师所在学校，并按学校名称首字母升序排序。

      
           SELECT t.school  FROM (
     SELECT sschool AS school
      FROM student
      UNION
      SELECT tschool AS school
      FROM teacher
  ) t
  ORDER BY t.school ASC;

回显如下：

使用UNION ALL（合并不去重）获取所有学生和教师所在学校，并按学校名称首字母升序排序。

      
           SELECT t.school  FROM (
     SELECT sschool AS school
      FROM student
      UNION ALL
      SELECT tschool AS school
      FROM teacher
  ) t
  ORDER BY t.school ASC;

使用UNION ALL（合并带有WHERE子句SQL结果集）获取来自“Tongji”的学生和教师的所有信息，并按学生和教师的编号升序排序。

      
           SELECT t.*  FROM  (
  SELECT Sid AS id,Sname AS name,Sgender AS gender,Sschool AS school
  FROM student
  WHERE Sschool='Tongji'
  UNION ALL
  SELECT Tid AS id,Tname AS name,Tgender AS gender,Tschool AS school
  FROM teacher
  WHERE Tschool='Tongji'
) t
  ORDER BY t.id ASC;