The UNION clause computes the set union of the rows returned by the involved SELECT statements. The SELECT statement inside the UNION clause must have the same number of columns, and the values of the columns must have similar data types. In addition, the sequence of columns in each SELECT statement must be the same.
Syntax
UNION: retains only one value if duplicate values exist in the results.
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2;
UNION ALL: displays all results, including duplicate values.
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2;
Examples
Take table1 and table2 as an example. The table data is as follows:
openGauss=# SELECT * FROM table1;
name | city
-------+------
Joes | BJ
Lily | BJ
James | SH
Grace | SZ
(4 rows)
openGauss=# SELECT * FROM table2;
id | name | dept
----+-------+-------
1 | Tommy | IT
2 | Lily | IT
3 | Li | sales
4 | Grace | IT
(4 rows)
UNION
Lists the names of all employees in two tables and retains only one duplicate value.
openGauss=# SELECT name FROM table1 UNION SELECT name FROM table2;
name
-------
Li
Lily
James
Grace
Joes
Tommy
(6 rows)
UNION ALL
Lists the names of all employees in two tables, including duplicate values.
openGauss=# SELECT name FROM table1 UNION ALL SELECT name FROM table2;
name
-------
Joes
Lily
James
Grace
Tommy
Lily
Li
Grace
(8 rows)
Top comments (0)