Union and Union all both are use for combine the result-set of two of more select queries.

Let us explain both individually

Union-

Union Operator is use for merging two or more select queries. It returns distinct rows from the result-set.

Let us explain by taking an example

Make two table in the database and insert following records.

select * from Test.dbo.table_1
union
select * from Test.dbo.table_2

output

In the above example of union operator we have seen that union operator

result distinct records from the two or more select statement. It eliminate

the repeated records from the result set. It Returns total 7 record                   

Union All-

Union All operator is use for combining the result set of two or more select

queries into one result set.

Ex.

select * from Test.dbo.table_1
union all
select * from Test.dbo.table_2

In the above example we have seen how to use union all operator. It returns

all the rows from the both table. It does not remove duplicate rows. It

returns total 8 records.

select Id,Email,Name from Test.dbo.table_1
union all
select Id,Name,Email from Test.dbo.table_2

In the above statement the order of column is mismatching that’s why it

does not executed successfully so while selecting column we have to take

care of ordering. And in both the select statement column number should

have same.

select Id,Email from Test.dbo.table_1
union all
select Id,Name,Email from Test.dbo.table_2

The  rules that we have to follow while using union               

1.) Number of columns and order of columns of all select statement must be same.

2.) The data types of the columns oftable in each statement must be same or compatible.

3.) It returned column names are taken from the first query.

 UNION behaves like UNION [DISTINCT] , i.e. remove the duplicate rows; however,

using ALL keyword with UNION returns all rows, including duplicates.

Difference between Union and Union all

1-Union eliminate repeated rows.

2-Union all does not remove duplicate rows.

Difference between Sql Join and Union

1.) The columns of joining tables may be different in JOIN but in UNION the number of columns and order of columns of all queries must be same.

2.) The UNION puts rows from queries after each other( puts vertically ) but JOIN puts the column from queries after each other (puts horizontally), i.e. it makes a cartesian product means if it has m row and n column then it returns m*n records.

You can also visit these related useful link

Sql union operator

Sql, Intersection and Except Operator in sql sever