SQL UNION Operator

The SQL UNION operator can be used to return distinct rows from two ‘select’ statements, that contain columns of the same type. To illustrate this, the ‘person’ table, from the previous examples will be used, along with a ‘person_archive’ table.

person
id firstname lastname title dob
1 Fred Bloggs Mr 1980-05-05
2 Simon Smith Mr 1960-04-01
3 Freida Bloggs Mrs 1970-10-12
4 Fiona Jones Miss 1985-05-19
person_archive
id firstname lastname title dob
1 Andrew Adams Mr 1979-06-20
2 John Johnson Mr 1965-11-10
3 Zoe Edwards Miss 1989-03-14
4 Fred Bloggs Mr 1980-05-05
5 Sally Simpson Mrs 1960-02-14
6 Fiona Jones Miss 1985-05-19

Two ‘select’ statements can be used to retrieve the data from the separate tables and the results combined with the UNION operator as follows.

SELECT firstname, lastname, title, dob
FROM person
WHERE dob BETWEEN '1980-01-01' AND '1989-12-31'
UNION
SELECT firstname, lastname, title, dob
FROM person_archive
WHERE dob BETWEEN '1980-01-01' AND '1989-12-31'
ORDER BY lastname, firstname, dob;

Here the two SQL statements return data where the date of birth is between 1 January 1980 and 31 December 1989. The ‘order by’ clause is placed at the end to order the combined results set.

firstname lastname title dob
Fred Bloggs Mr 1980-05-05
Zoe Edwards Miss 1989-03-14
Fiona Jones Miss 1985-05-19

Notice that ‘Fred Bloggs’ and ‘Fiona Jones’ only appear once in the results set despite them being present in both tables.

A variation on the UNION operator is UNION ALL, which allows for duplicates to be returned.

SELECT firstname, lastname, title, dob
FROM person
WHERE dob BETWEEN '1980-01-01' AND '1989-12-31'
UNION ALL
SELECT firstname, lastname, title, dob
FROM person_archive
WHERE dob BETWEEN '1980-01-01' AND '1989-12-31'
ORDER BY lastname, firstname, dob;

Two records now appear in the results for ‘Fred Bloggs’ and ‘Fiona Jones’.

firstname lastname title dob
Fred Bloggs Mr 1980-05-05
Fred Bloggs Mr 1980-05-05
Zoe Edwards Miss 1989-03-14
Fiona Jones Miss 1985-05-19
Fiona Jones Miss 1985-05-19

In order to show in the results set, which table a record has come from, an extra column can be added that doesn’t exist in the underlying data.

SELECT firstname, lastname, title, dob, 'Current' AS type
FROM person
WHERE dob BETWEEN '1980-01-01' AND '1989-12-31'
UNION ALL
SELECT firstname, lastname, title, dob, 'Archive' AS type
FROM person_archive
WHERE dob BETWEEN '1980-01-01' AND '1989-12-31'
ORDER BY lastname, firstname, dob;

An extra column, ‘type’, has been added to both the ‘select’ statements, that displays ‘Current’ for the ‘person’ table and ‘Archive’ for the ‘person_archive’ table.

firstname lastname title dob type
Fred Bloggs Mr 1980-05-05 Archive
Fred Bloggs Mr 1980-05-05 Current
Zoe Edwards Miss 1989-03-14 Archive
Fiona Jones Miss 1985-05-19 Archive
Fiona Jones Miss 1985-05-19 Current