SQL: UNION V/S UNION ALL


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;