Difference b/w Union and Union All

Difference b/w union and union All in SQL Server.

Difference b/w Union and Union All

There are two tables, table1 and table2
 
Now, the query for union is,
 
select * from Table1
UNION
select * from Table2
 
This query gives the distinct result and in a sorted format. The result is shown in the above picture.
Now, the query for union All,
 
select * from Table1
UNION ALL
select * from Table2
 
This query gives all the rows and in an Unsorted format. The result is shown in the above picture.
 
1. For union and union All to work, the member, datatype, and the order of columns in the selected statement, should be same.
 
2. We cannot use the order by in the union All for sorting at the initial, as it gives error.
 
select * from table1
order By name (Can not do)
select * from tabl2
union All 
 
select * from table1
union All  (Can do) 
select * from table2
order By name 
 
Union combines the row from 2 or more tables where the join combines the column from 2 or more tables.