UNION and UNION ALL in PostgreSQL



UNION Operator in Postgres

The UNION operator is responsible for combining result sets of two or more than two SELECT statements into a single result set. It also removes all the duplicate rows.

Syntax of UNION Operator


SELECT Col_1, Col_2
FROM Tbl_1
UNION
SLECT Col_1, Col_2
FROM Tbl_2
UNION
SELECT Col_1, Col_2
FROM Tbl_3


When using the UNION operator in PostgreSQL, we have to follow some rules.

1- All Queries (Which SELECT statements result set wan to combine) should return the same number of columns


2- The corresponding columns in the queries must have compatible data types (For example Col_1 of Tbl_1 and Col_1 of Tbl_2 should be compatible).

UNION Operator in Postgres



The UNION ALL operator is also responsible for combining the result sets of two or more than two SELECT statements into a single result set but it does not remove the duplicate row like UNION operator.

Syntax of UNION ALL operator



SELECT Col_1, Col_2
  FROM Tbl_1
  UNION ALL
SLECT Col_1, Col_2
  FROM Tbl_2
  UNION ALL
SELECT Col_1, Col_2
  FROM Tbl_3



This has the same rule of UNION operator like all queries should return the same number of columns and corresponding columns in the queries must have compatible data types.


For the example, I am creating an EMPLOYEE table which containing some columns and records such as First Name, Last Name, User Name, Password, contact, salary, Date of Joining, Manager ID, and Age.


CREATE TABLE employee
(
  empid serial not null,
  fname text,
  lname text,
  username text,
  password text,
  contact text,
  salary numeric,
  doj date,
  managerid integer,
  age integer,
  constraint pk_employee_empid Primary key(empid)
);

-- Inserting some data into EMPLOYEE table

INSERT INTO employee (fname, lname, username, password, contact, salary, managerid, age)
  VALUES('Naina','Singh', 'naina@gmail.com','test1234','9012234439',120000,0,40);

INSERT INTO employee (fname, lname, username, password, contact, salary, managerid, age)
  VALUES('Ashish','Singh', 'ashish@gmail.com','test1234','9887778876',90000,1,35);

INSERT INTO employee (fname, lname, username, password, contact, salary, managerid, age)
  VALUES('Rahul','Jain', 'rahul@gmail.com','test1234','4556676643',70000,1,30);

INSERT INTO employee (fname, lname, username, password, contact, salary, managerid, age)

  VALUES('Ravish','Jha', 'ravish@gmail.com','test1234','9897765565',40000,2,25);


UNION operator example



SELECT fname, lname, managerid
  FROM public.employee WHERE fname LIKE '%Ra%'
UNION
SELECT fname, lname, managerid
  FROM public.employee WHERE salary > 100000;               


Result:



Now we will check, it removes the duplicate records or not.


I am changing the second SELECT statement (where clause) so that it returns duplicate records that already exist in the first SELECT statement result set.


SELECT fname, lname, managerid
  FROM public.employee WHERE fname LIKE '%Ra%'
  UNION
SELECT fname, lname, managerid

  FROM public.employee WHERE salary > 30000;


Result:




There are two records are duplicate (Rahul and Ravish) but UNION operator removed the duplicate records.

UNION ALL operator examples:



If I execute the above records with UNION ALL operator, its returns the duplicate records.


SELECT fname, lname, managerid
  FROM public.employee WHERE fname LIKE '%Ra%'
UNION ALL
SELECT fname, lname, managerid

  FROM public.employee WHERE salary > 40000;


Result:


EXPLAIN ANALYZE of UNION and UNION ALL operator



EXPLAIN ANALYZE SELECT fname, lname, managerid
  FROM public.employee WHERE fname LIKE '%Ra%'
UNION
SELECT fname, lname, managerid

  FROM public.employee WHERE salary > 40000;


Result:




EXPLAIN ANALYZE SELECT fname, lname, managerid
  FROM public.employee WHERE fname LIKE '%Ra%'
UNION ALL
SELECT fname, lname, managerid

  FROM public.employee WHERE salary > 40000;


Result:




If we do the analysis of the above queries (Union and UNION ALL)

We can see UNION ALL performance is better than UNION operator, For the same query execution time is not same, UNION operator execution time is .084 ms (millisecond) while UNION ALL operator execution time is .044 ms which half almost. So use these operators carefully in your query.

Related Posts

What is the Use of isNaN Function in JavaScript? A Comprehensive Explanation for Effective Input Validation

In the world of JavaScript, input validation is a critical aspect of ensuring that user-provided data is processed correctly. One indispensa...