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.