Please enable Javascript for better experience...
Difference between Union and Union All in SQL
By Rahul Kumar Jha | Apr 27, 2020 | In Articles | Total Views [ 142 ]
Taged In
(0 Like)
Rate

Union and Union All are used to combine two results in SQL having similar structure.

Introduction

There are many instances where you may require to combine result of two queries. To achieve this, use "Union" and "Union All" keywords. Though these keywords are similar in nature but there is minor difference between these two i.e. union filters distinct results whereas union all show all record including duplicates.

Explaination using examples

Let's understand by simple examples. 

union shows distinct records but union all shows duplicate records from two set of queries.

Table structure

Table User
userid name
1 XYZ
2 ABC

Let'see below query.

select * from user where userid=1 union select * from user where userid in (1,2)

Above query will return two record for user having userid 1 and 2

userid name
1 XYZ
2 ABC

Now let see this query. This is same but only have different keyword

select * from user where userid=1 union all select * from user where userid in (1,2)

Above query will return three record for user having userid 1, 1 and 2

userid name
1 XYZ
1 XYZ
2 ABC

Share this

About the Author

Rahul Kumar Jha
Rahul Kumar Jha
Founder, Developer dotnet-concept.com

Public profile: user/profile/99900001


Has working experience in different phases of Software Development Life Cycle (SDLC) in CMS, Gaming, Health Care and Financial Services domain using Agile pattern. Working experience in Design patterns, ASP.NET, MVC, ANGULAR, ANGULAR JS, Windows application, WCF, ADO.NET, SQL Server and Test Driven Development (TDD) environment with JQuery, JavaScript, N-Unit, Entity Frameworks, LINQ, Code Refactoring and Business Objects Models.

User's Comments


 
Please SignUp/Login to comment...

Or comment as anonymous...
* Name
* Email ID
Comment
 
 
 
 
 
 
Sponsors