The UNION Operator is used to combine the result set of two or more SELECT Statement. This operator is useful if you have two table that share the same column name and data type and you want to combine it in a single Query.
Take note that both table must have the same column name and data type.
The difference between UNION and UNION ALL is that UNION will return a unique result or will eliminate duplicate record, while UNION ALL will return all records.
Consider the following table for this exercise
Teachers_Assistants
Firstname | Lastname | Salary | DeptID |
---|---|---|---|
John | Smith | 1000 | 1 |
Mathew | Simon | 3000 | 1 |
Bill | Steve | 2200 | 1 |
Amanda | Rogers | 1800 | 2 |
Steve | Hills | 2800 | 2 |
Steve | jobs | 2400 | 2 |
bill | cosby | 700 | 3 |
Faculty
Firstname | Lastname | Salary | DeptID |
---|---|---|---|
Ishaq | Raza | 4000 | 1 |
Waqar | Ahmed | 3000 | 1 |
S.M | Hussnani | 4200 | 1 |
Steve | Hills | 5000 | 1 |
Result of the Query
Firstname | Lastname |
---|---|
John | Smith |
Mathew | Simon |
Bill | Steve |
Amanda | Rogers |
Steve | Hills |
Steve | jobs |
bill | cosby |
Ishaq | Raza |
Waqar | Ahmed |
S.M | Hussnani |
The result returns only 10 rows since Steve Hills exist on both tables.
SQL UNION ALL Example
Result of the Query
Firstname | Lastname |
---|---|
John | Smith |
Mathew | Simon |
Bill | Steve |
Amanda | Rogers |
Steve | Hills |
Steve | jobs |
bill | cosby |
Ishaq | Raza |
Waqar | Ahmed |
S.M | Hussnani |
Steve | Hills |
The result returns all records from both tables with duplicate entry of Steve Hills.