Difference Between Union and Union all

     Union and Union all both the command is used concatenate the two different SQL result. Then what will be the difference let see…

Union

The union is the command used to link the two SQL Result. But it removes all the Duplicate data. For example, Here I am taking two tables from AdventureWorks2008 Database and one is currency Table and another one is Currency rate table. In that table, I Taking Currency code and ToCurrencyCode Data.
Select * from sales.Currency

Select * from sales.CurrencyRate 

Example For Union:-

Select CurrencyCode from Sales.Currency Union Select FromCurrencyCode from Sales.CurrencyRate

You can see it will all the duplicate in the table. In Below picture only 105 rows are affected.

Example For Union

Union All

  Union All doing the same job, But unlike Union it shows all the rows of the Data’s in two tables. It will not remove any duplicate rows in the table.

Example for Union all:
 Select CurrencyCode ,Name from Sales.Currency Union all Select FromCurrencyCode,ToCurrencyCode from Sales.CurrencyRate

Example For Union All

With this example, I think you get clear about union and union all command’s. If you have any suggestion please let me know. See next time with another post…..