The SQL UNION command is used to gather the entries from two tables which meet the given criteria.
The SQL Union command is used to select entries from two tables It requires that the items in each of the select are in the same order and that each comparable item in the same position is of the same data type and length.
Consider a typical example of two sets of employees located in two different countries, those in England and France:
England – table name = Users_GB
| Ref. (UserId) | First Name (FName) | Last Name (LName) |
|---|---|---|
| 006 | Peter | Smith |
| 008 | Steven | Jones |
| 023 | Ruth | Mortimer |
| 034 | Kurt | Fulmore |
| 184 | Linda | Loveridge |
France – table name = Users_FR
| Ref. (UserId) | First Name (FName) | Last Name (LName) |
|---|---|---|
| 007 | Linda | Loveridge |
| 026 | Louisa | Revel |
| 027 | Mark | Mitchell |
| 101 | Alan | Brown |
| 128 | James | Woodrow |
SELECT FName,LName FROM Users_GB UNION SELECT FName,LName FROM Users_FR
As given above the command will select distinct entries, where entries are common to both tables only one entry will be returned.
UNION combined table
| First Name (FName) | Last Name (LName) |
|---|---|
| Peter | Smith |
| Steven | Jones |
| Ruth | Mortimer |
| Kurt | Fulmore |
| Linda | Loveridge |
| Louisa | Revel |
| Mark | Mitchell |
| Alan | Brown |
| James | Woodrow |
To return all entries from both tables the Union all command is used:
SELECT FName,LName FROM Users_GB UNION ALL SELECT FName,LName FROM Users_FR
UNION ALL combined table
| First Name (FName) | Last Name (LName) |
|---|---|
| Peter | Smith |
| Steven | Jones |
| Ruth | Mortimer |
| Kurt | Fulmore |
| Linda | Loveridge |
| Linda | Loveridge |
| Louisa | Revel |
| Mark | Mitchell |
| Alan | Brown |
| James | Woodrow |
