SQL Union Operator

Submitted by admin on Tuesday, April 26, 2011 - 10:37.

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.

SQL Union Operator

SQL UNION Syntax

SQL UNION ALL Syntax

SQL UNION Example

Add new comment