Skip to main content

UNION and UNION ALL in PostgreSQL


UNION Operator in Postgres

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

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 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 to combine 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 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.