EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Head to Head Differences Tutorial PostgreSQL union vs union all
 

PostgreSQL union vs union all

Updated May 30, 2023

PostgreSQL union vs union all

 

 

Difference Between PostgreSQL union vs union all

PostgreSQL union: UNION operator in PostgreSQL combines the result of two or more SELECT statements and removes the duplicate rows. Before the final result of the UNION operation, DISTINCT is applied to the resultset, which removes the duplicate rows from it and displays only the relevant data to the user.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

PostgreSQL union all: UNION ALL operator in PostgreSQL is also used to combine the result of two or more SELECT statements, but it does not perform any additional operation on the resultset. Instead, it returns the full table by concatenating the results of SELECT statements and displaying them to the user.  It is somewhat faster than the UNION operator as no extra overhead of removing duplicates needs to be performed.

Head to Head Comparison Between PostgreSQL union vs union all (Infographics)

Below is the Top Comparison Between PostgreSQL union vs union all:

PostgreSQL-union-vs-union-all-info

Key Differences

Some of the key differences between union and union all in PostgreSQL describing the detailed description are given below:

1. Both the UNION and UNION ALL operators are used in PostgreSQL to combine two tables by merging the rows returned by two or more SELECT statements. The only difference is that UNION does not return or removes the duplicate rows, whereas UNION returns all the rows returned after merging, either duplicate or not.

2. The UNION operator performs the DISTINCT operation on the resultset, removing the duplicate rows from it. So, in case of a large volume of data present in the tables, the UNION operator is considered to be slower than the UNION ALL operator as it hampers the overall speed by performing the additional overhead of the DISTINCT operation.

3. To perform both the UNION and UNION ALL operations for combining the tables, the same conditions need to be satisfied:

  • The same number of columns should be retrieved from each SELECT statement that is to be combined.
  • Columns retrieved from each SELECT statement must be of the same data type.
  • Columns retrieved from each SELECT statement must be in the same order.

4. Let us understand the difference in the result set returned by the UNION and UNION ALL operators with the help of an example:

Consider the two tables with the name ‘ITDepartment’ and ‘OperationsDepartment’ having the fields ’emp_id’ and ’emp_name’

ITDepartment:

emp_id emp_name
E_001 Arush
E_002 Akash
E_003 Amitansh
E_004 Gourang
E_005 Manish

OperationsDepartment:

emp_id emp_name
E_006 Akansha
E_007 Atul
E_008 Amrish
E_004 Gourang
E_009 Akshat

Query using the UNION operator:

SELECT * from ITDepartment
UNION
SELECT *  from OperationsDepartment;

Result:

 output 1

In the above tables, Employee ‘Gourang’ having emp_id ‘E_004’ is working in both IT and Operations Department, so present in both the tables, i.e., ‘ITDepartment’ and ‘OperationsDepartment’. Since the UNION operator on both tables’ SELECT statement returns a combined result set of both tables and removes the duplicate rows. So, performing the UNION on the resultset of both the tables, the results displaying the employee ‘Gourang’ only once.

Query using UNION ALL operator:

SELECT * from ITDepartment
UNION ALL
SELECT *  from OperationsDepartment;

Result:

Output 2

In the above tables, Employee ‘Gourang’ having emp_id ‘E_004’ is working in both IT and Operations Department, so present in both the tables, i.e., ‘ITDepartment’ and ‘OperationsDepartment’. Since the UNION ALL operator on both tables’ SELECT statement returns a combined result set of both tables. Still, it does not remove the duplicate rows, it instead displays all the data resulting by concatenation. So

performing the UNION  ALL on the resultset of both the tables, the results display the employee ‘Gourang’ two times. If it is present ‘n’ number of times, UNION ALL will return all of them.

Comparison Table

Below given is the comparison table showing the head to head comparison between union and union all in PostgreSQL:

S.No. PostgreSQL union PostgreSQL union all
1.  Union operator in PostgreSQL combines the result set of two or more SELECT statements. Duplicate rows returned from the result of SELECT statements are eliminated first and combined to give the unified result. Union all operators in PostgreSQL combine the result of two or more SELECT statements without removing the duplicate rows returned from each SELECT.
2. Union operator in PostgreSQL eliminates the duplicate rows returned by merging the two SELECT statements. Union all operator does not remove the duplicate rows  (if present) in the result obtained by merging the two SELECT statements.
3. In case of many duplicates, the union operator works great, showing only the relevant data to the user. Union all operator is slower than the union operator in case of a large number of duplicates resulting from querying, as processing duplicates may exceed the query execution time.
4. Sometimes, processing time gets slower in the case of union operators as there is an extra overhead of performing the DISTINCT on the result set. In the case of processing time, the union all operator is faster as it does not remove the duplicate rows in the result set.
5. Syntax of using the union operator in PostgreSQL is:

 

SELECT exp1, exp2, exp3, . . .

from tab1

UNION

SELECT exp1, exp2, exp3, . . .

from tab2

 

Where,

exp1, exp2, exp3, . . . are the column names that need to be retrieved from tab1 and tab2 respectively.

tab1: It is the first table name on which a SELECT statement will be applied.

tab2: It is the second table name on which a SELECT statement will be applied.

Syntax of using the union all operator in PostgreSQL is:

 

SELECT exp1, exp2, exp3, . . .

from tab1

UNION ALL

SELECT exp1, exp2, exp3, . . .

from tab2

 

Where,

exp1, exp2, exp3, . . . are the column names that need to be retrieved from tab1 and tab2 respectively.

tab1: It is the first table name on which a SELECT statement will be applied.

tab2: It is the second table name on which a SELECT statement will be applied.

Conclusion

The above description clearly explains what the PostgreSQL union and union all is and the major differences between the two. Both the UNION and UNION ALL operators are used for the same purpose in PostgreSQL (though the output is different). It depends on the programmer’s choice, the data present in the tables, and the required output for the user to use any of them according to their requirements.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL union vs union all” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. OData vs GraphQL
  2. Zendesk vs Freshdesk
  3. Aerospike vs Redis
  4. Program vs Process

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW