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.
