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)
    
Feedback
编组 3备份
    openGauss 2024-04-21 00:47:23
    cancel