PostgreSQL – UNION operator

The PostgreSQL UNION operator is used to combine result sets of multiple queries into a single set of result. It is used to combine result sets of two or more SELECT statements into a single result set.

Syntax:
SELECT
    column_1,
    column_2
FROM
    table_name_1
UNION
SELECT
    column_1,
    column_2
FROM
    table_name_2;

The below rules need to be followed while using a UNION operator:

• Both queries must return the same number of columns.
• The corresponding columns in the queries must have compatible data types.

Note: The UNION operator removes all duplicate rows from the query set.

Let’s look into some examples of the UNION operator by setting up two sample tables in a sample database(say, sales2020). Let’s say table “sales2020q1” represents the sales of a particular product in the first quarter of 2020 and “sales2020q2” represents the sales in the second quarter of the same year. Now let’s set up the database following the below procedures:

• Create the sales2020 database using the below command:

  CREATE DATABASE sales2020;

• Now add two tables namely sales2020q1 and sales2020q2 to the sales2020 database using the below commands:

  CREATE TABLE sales2020q1(
      id SERIAL,
      name VARCHAR(50) NOT NULL,
      amount VARCHAR(50),
      PRIMARY KEY (id)
  );
  CREATE TABLE sales2020q2(
      id SERIAL,
      name VARCHAR(50) NOT NULL,
      amount VARCHAR(50),
      PRIMARY KEY (id)
  );

• Insert data into tables using the below commands:

  INSERT INTO sales2020q1(name, amount)
  VALUES
      ('Raju Kumar', '121232'),
      ('Nikhil Aggarwal', '254789'),
      ('Abhishek Kelenia', '365487');
  
  INSERT INTO sales2020q2(name, amount)
  VALUES
      ('Raju Kumar', '457264'),
      ('Nikhil Aggarwal', '365241'),
      ('Abhishek Kelenia', '759864');
  

Now that our sample database is ready. Let’s implement the UNION operator in a few examples.

Example 1:
Here we will use the UNION operator to combine data from both sales2020q1 and salese2020q2 tables.

SELECT *
FROM
    sales2020q1
UNION
SELECT *
FROM
    sales2020q2;

Output:

Example 2:
Here we will sort the combined result returned by the UNION operator in defending order of “id” by using the ORDER BY clause after combining the data from both sales2020q1 and salese2020q2 tables.

SELECT *
FROM
    sales2020q1
UNION ALL
SELECT *
FROM
    sales2020q2
ORDER BY 
 name ASC,
 amount DESC;

Output: