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 |