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.