Usage of UNION¶

UNION is a powerful SQL operator that combines the result sets of two or more SELECT statements into one. During combination, the number of columns and data types in the two tables must be the same and correspond to each other. Use the UNION or UNION ALL keyword between SELECT statements.

UNION removes duplicate rows, while UNION ALL keeps them. Deduplication is time-consuming, so UNION ALL can be faster than UNION if the data sets are already distinct by logic.

Syntax¶

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

Example¶

Create the student information table student (ID, name, gender, and school).

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

Insert data into the student table.

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

View the student table.
```
SELECT * FROM student;
```
Information similar to the following is displayed.

Create the teacher information table teacher (ID, name, gender, and school).

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

Insert data to the teacher table.

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

Query the teacher table.
```
SELECT * FROM teacher;
```

Use UNION (combine and deduplicate) to obtain the schools of students and teachers and sort the schools in ascending order by initial letter of the school name.

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

Information similar to the following is displayed.

Use UNION ALL (combine without deduplication) to obtain the schools of all students and teachers and sort the schools by initial letter of the school name in ascending order.

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

Use UNION ALL (combine the result sets of SQL statements with WHERE clause) to get all information about students and teachers from "Tongji' and sort by student and teacher number in ascending order.

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

Summary¶

In actual service scenarios, pay attention to the following points when using UNION and UNION ALL:

The number of SQL fields and field types on the left and right sides must be the same.
Check whether data deduplication (deduplication before combination or during combination) is needed based on service requirements.
Based on the data volume, valuate the SQL execution efficiency and determine whether to use temporary tables.
Select UNION or UNION ALL wisely and consider the complexity when writing SQL statements.

last updated: 2024-11-13 15:46 UTC - commit: bdcb01d62e4f1de672b662d88ef6cb8eb3a27a29