PostgreSQL - UNION Operator

---

---

In PostgreSQL, UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows. The duplicate rows never display to the result set.

To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order but they do not have to be the same length.

Syntax

Following is the basic syntax UNION is as follows −

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Here,

• Given condition could be any given expression based on your requirement.

Example

Consider the following two tables:

(a) COMPANY table is as follows −

(b) Another table is DEPARTMENT as follows −

testdb=# SELECT * from DEPARTMENT;
 id | dept        | emp_id
----+-------------+--------
  1 | IT Billing  |      1
  2 | Engineering |      2
  3 | Finance     |      7
  4 | Engineering |      3
  5 | Finance     |      4
  6 | Engineering |      5
  7 | Finance     |      6
(7 rows)

Now let us join these two tables using SELECT statement along with UNION clause as follows −

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION
      SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID;

The above query obtained the following result −

The UNION ALL Clause

The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows. The same rules that apply to UNION apply to the UNION ALL operator as well.

Syntax

The basic syntax of UNION ALL is as follows −

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Here,

• Given condition could be any given expression based on your requirement.

Example

In this example, we generate the query based on two tables in SELECT statement as follows −

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION ALL
      SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID;

This above query obtained the following result −

PostgreSQL UNION ALL with ORDER BY clause

While sorting the returning result from UNION operator, use ORDER BY clause after the second query.

Syntax

Following is the syntax of ORDER BY clause associated with UNION ALL in PostgreSQL −

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
ORDER BY column1 [, column2 ] [ASC | DESC];

Example

Below the query shows how to use ORDER BY with UNION ALL to sort the combined results by employee name.

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION ALL
      SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID
   ORDER BY NAME;

The above query obtained the following result −