Here I will explain difference between UNION and UNION ALL in SQL server. Union operator will return unique records from tables and Union All in SQL serverwill return all the records from the tables including duplicate values also.

SQL Union Operator is used to combine the result of two or more select statement queries into single result set. The Union Operator is used to select only distinct values from two tables.

SQL Union Operator Syntax:

SELECT column1,column2 FROM table1

UNION

SELECT column1,column2 FROM table2

Here one more thing we need to remember that is we can use Union Operator for the tables which is having same column names and same data types otherwise it will throw error like this

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Now I will explain with one example first design two tables in your tables like this

UserInfo

UserID

UserName

Location

1

Suresh

Hyderabad

2

Prasanthi

Hyderabad

3

Mahesh

Vizag

After that create another table and give name as UserDetails

UserID

UserName

Location

1

Suresh

Hyderabad

2

Nagaraju

Bangalore

3

Madhav

Nagpur

Now write the Union Operator Query to get all the user details from two tables like this

SELECT UserName,Location FROM UserInfo

UNION

SELECT UserName,Location FROM UserDetails

Resultant table will be like this

UserName

Location

Suresh

Hyderabad

Prasanthi

Hyderabad

Mahesh

Vizag

Nagaraju

Bangalore

Madhav

Nagpur

If you observe above resultant table it contains UserDetails with distinct records because Union Operator will return only distinct records. If we want all the records then we need to use UNION ALL Operator.

SQL UNION ALL Operator:

This operator is used in a situation like return all the records from the tables including duplicate values also.

SQL UNION ALL Operator Syntax:

SELECT column1,column2 FROM table1

UNION ALL

SELECT column1,column2 FROM table2

Result table

UserName

Location

Suresh

Hyderabad

Suresh

Hyderabad

Prasanthi

Hyderabad

Mahesh

Vizag

Nagaraju

Bangalore

Madhav

Nagpur

The main difference between Union and Union ALL operator is

Union operator will return distinct values but Union ALL returns all the values including duplicate values.