Introduction to UNION in PostgreSQL
Union in PostgreSQL is used to combine the result of two or more select queries without returning duplicate values, union is most important in PostgreSQL. To use this function each select statement must have the same number of columns selected and the same no of column expressions but don’t need to have the same length. Union in PostgreSQL will remove duplicate values from the table and give output to the user. We can combine one or more queries in the result set.
Syntax:
Below is the syntax which is as follows.
Select Column_name1, Column_name2, …, Column_nameN from table1
UNION
Select Column_name1, Column_name2, …, Column_nameN from table2
Select expression1, expression2, …, expressionN from table1
UNION
Select expression 1, expression 2, …, expressionN from table2
Select * from table1
UNION
Select * from table2
Below is the parameter description of the above syntax which is as follows.
- Select: Select statement is used to select no of the column from tables.
- Column1 to ColumnN: Column used in the select statement to fetch result using union in
PostgreSQL. - Expression1 to ExpressionN: Column used in the select statement to fetch result.
- Table1 and Table2: Table used to retrieve data using Union operator in PostgreSQL.
- UNION: UNION operator used to combine results of two or more queries.
- From: Keyword to define table from which we have fetching data.
- Asterisk (*): Retrieve all columns in a result set from the specified table.
How does UNION Clause work in PostgreSQL?
- If we have to combine results using the UNION clause then both the query must have the same row count to retrieve the output.
- Also in both, the columns need to have compatible data types.
- Union operator is used to combining results from one or multiple tables.
- We have used a union clause or operator to combine data from a similar table that was not perfectly normalized. Such tables were found in database warehouse applications.
- The union operator in PostgreSQL places the rows in the first query after, between, or before to the second used query.
- We also use order by clause to sort the result as ascending or descending order.
- Union clause is used to remove or eliminate duplicate records from the table.
Examples to Implement
We have used employee_test1 and employee_test2 tables to describe.
Example #1 – Employee_Test1
Code:
CREATE TABLE Employee_Test1 ( emp_id INT NOT NULL, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), emp_salary INT NOT NULL, date_of_joining date NOT NULL, PRIMARY KEY (emp_id));
INSERT INTO Employee_Test1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (1, 'ABC', 'Pune', '1234567890', 20000, '01-01-2020');
INSERT INTO Employee_Test1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (2, 'PQR', 'Pune', '1234567890', 20000, '01-01-2020');
INSERT INTO Employee_Test1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (3, 'XYZ', 'Mumbai', '1234567890', 35000, '02-01-2020');
INSERT INTO Employee_Test1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (4, 'BBS', 'Mumbai', '1234567890', 45000, '02-01-2020');
INSERT INTO Employee_Test1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (5, 'RBS', 'Delhi', '1234567890', 50000, '03-01-2020');
Output:
Example #2 – Employee_Test2
Code:
CREATE TABLE Employee_Test2 ( emp_id INT NOT NULL, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), emp_salary INT NOT NULL, date_of_joining date NOT NULL, PRIMARY KEY (emp_id));
INSERT INTO Employee_Test2 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (6, 'PQR', 'Pune', '1234567890', 20000, '01-01-2020');
INSERT INTO Employee_Test2 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (7, 'XYZ', 'Mumbai', '1234567890', 35000, '02-01-2020');
INSERT INTO Employee_Test2 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (8, 'BBS', 'Mumbai', '1234567890', 45000, '02-01-2020');
INSERT INTO Employee_Test2 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (9, 'RBS', 'Delhi', '1234567890', 50000, '03-01-2020');
INSERT INTO Employee_Test2 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (10, 'ABC', 'Pune', '1234567890', 20000, '01-01-2020');
Output:
select * from Employee_Test1;
select * from Employee_Test2;
Please find below an example of a UNION operator in PostgreSQL.
Union operator using all columns from both the table.
- In the below example, we have retrieved data from all the columns. In such a case, all data is combined with employee_test1 and employee_test2 tables.
- In this scenario, all records will be retrieved from both tables because in some columns data is different.
testing=# select * from Employee_Test1 UNION select * from Employee_Test2;
Union operator using a specific column from both the table.
- In the below example we have retrieved data from specific columns. In such a case, only distinct records from both the tables are fetched.
- Duplicate records from both tables are eliminated or removed.
- In this scenario, only distinct records will be retrieved from both tables because in some columns data is duplicated.
testing=# select emp_name, emp_address from Employee_Test1 UNION select emp_name, emp_address from Employee_Test2;
Union clause using ORDER BY clause in PostgreSQL.
- To sort result combined from both the query at that time we have used order by clause in PostgreSQL.
- Using order by clause we sort the result data with ascending and descending order.
- Below example shows union clause using order by clause in PostgreSQL.
testing=# select * from Employee_Test1 UNION select * from Employee_Test2 order by emp_id ASC;
testing=# select * from Employee_Test1 UNION select * from Employee_Test2 order by emp_id DESC;
Rules for Using UNION in PostgreSQL
- Below are the rules and regulations while using the union clause in PostgreSQL.
- We need the same column in both tables while using UNION to combine the results of one or more queries.
- Also need same data type in both the table column list while combining the result.
- UNION clause or operator is very useful and important in PostgreSQL to combine the result of one or more queries.
- For combining the result we need to select the same column from both tables.
- To sort result by ascending and descending order we use order by clause in UNION operator.
Conclusion
UNION clause is very useful and important in PostgreSQL to combine the result of one or more queries. We have sorted the data in ascending and descending order. We are using order by clause in the UNION operator. We need the same column to combine the result of one or more queries.
Recommended Articles
This is a guide to a UNION in PostgreSQL. Here we discuss the Introduction and working of UNION in PostgreSQL along with different examples. You may also look at the following articles to learn more –
