SQL “Union” and “Union All”

-->

SQL “Union” and “Union All”

SQL union combines two or more tables. Let’s say, I have two tables Persons_BD and Persons_US

They both have similar column names that I will try to combine with Union query.  Persons_US has data like:

P_Id	LastName	FirstName	Address	City
1	Chowdhury	Pavel	3326 82ns Street	Sandnes
2	Chowdhury	Nitol	Dhaka Street	Dhaka

And Person_BD have data like

P_Id	LastName	FirstName	Address	City
2	Chowdhury	Nitol	Dhaka Street	Dhaka
2	Chowdhury	Pavel	Dhaka Street	Dhaka

Now, if I run this query

SELECT FIRSTNAME +'  '+ LASTNAME AS NAME FROM DBO.PERSONS

UNION

SELECT FIRSTNAME +'  '+ LASTNAME FROM DBO.PERSONS1

The result set will be

NAME

Nitol  Chowdhury

Pavel  Chowdhury

However, if I use UNION ALL look the result set is different

SELECT FIRSTNAME +'  '+ LASTNAME AS NAME FROM DBO.PERSONS

UNION ALL

SELECT FIRSTNAME +'  '+ LASTNAME FROM DBO.PERSONS1

NAME

Pavel  Chowdhury

Nitol  Chowdhury

Nitol  Chowdhury

Pavel  Chowdhury

This is because UNION compare the tables and bring the result only the information that are common.