SQL Union Operator

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.

SQL UNION Syntax

SELECT column_name(s) FROM First_table_name UNION SELECT column_name(s) FROM Second_table_name

SQL UNION ALL Syntax

SELECT column_name(s) FROM First_table_name UNION ALL SELECT column_name(s) FROM Second_table_name

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

SQL UNION Example

SELECT Firstname, Lastname FROM Teachers_Assistants UNION SELECT Firstname, Lastname FROM Faculty

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

SELECT Firstname, Lastname FROM Teachers_Assistants UNION ALL SELECT Firstname, Lastname FROM Faculty

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.

Add new comment