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

Faculty

SQL UNION Example

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

Result of the Query

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

The result returns all records from both tables with duplicate entry of Steve Hills.