UNION Clause

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)