Union and Union All are used to combine two results in SQL having similar structure.
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.
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 |
