In SQL Server, by using UNION and UNION ALL we can combine multiple datasets into one comprehensive dataset. But there is a major difference between the two:
UNION: Union command will allow you to join multiple datasets into one dataset and will remove any duplicates that exist.
Syntax:
select field1, field2, . field_n
from tables
UNION
select field1, field2, . field_n
from tables;
UNION ALL: Union all also do the same thing but will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.
Syntax:
select field1, field2, . field_n
from tables
UNION ALL
select field1, field2, . field_n
from tables;