Union OR Union All

SQL Union is a set operator that  can be used in SQL Query where you want to combine the result set from two or more Select Statements or similar tables.

Union Syntax:
The syntax is pretty much the same for Oracle, SQL Server and MySQL.

Select <Column Names> From TABLE 1
Where condition
UNION
Select <Column Names> From TABLE 2
Where condition
UNION
Select <Column Names> From TABLE 3
Where condition

For the demonstration purposes only, let's say that you have two different table named HR_Team and IT_Team, These table stores data on the Team Names Configured for HR department and IT department. Your task for the day is to get the list of all the team names. To accomplish your task you will have to use the UNION operator to combine the result set of two select statements that selects the team name column  from each table.

 Lets do some practical exercise so it makes more sense on what I am taking about.

Data from IT_Team table

Select * From [AdventureWorks2014].[dbo].[IT_Team]

Result:

Data from HR_Team table

Select * From [AdventureWorks2014].[dbo].[HR_Team]

Result:

Query to get the List .

Syntax:

Select TeamName
From [AdventureWorks2014].[dbo].[IT_Team]
UNION
Select TeamName
From [AdventureWorks2014].[dbo].[HR_Team] END

Result:

If you take a closer look at the result then you will notice that only 9 rows were returned back but  we have total of 10 teams. Now you might be thinking what happened to the remaining one team. Please refer to the team names on both the table and compare the team names. You will find out that  "TeamRanger"  exists twice. So this means UNION will only select the Distinct Value, to select everything all you have to do is Replace UNION with UNION ALL .

UNION Vs UNION ALL:
UNION will ignore the duplicates whereas UNION ALL will return all the rows back.

Result we get back by using UNION ALL

Syntax: 

Select TeamName
From [AdventureWorks2014].[dbo].[IT_Team]
UNION ALL
Select TeamName
From [AdventureWorks2014].[dbo].[HR_Team]

Result:

Things to remember when using UNION

1. All the Select statements used in the UNION  must have same number of columns and same data type.
2. The columns in the Select statement should be in the same order.
3. Performance wise UNION ALL is better than UNION as it doesn't have to filter anything.