Combining Query Results with the UNION Command

SQL’s UNION command allows you to combine the results of two or more database queries that are not necessarily linked through a database relationship. For example, imagine that you have a school database and wish to use it to create a master contact list for all students, faculty and staff. Looking at your database, you discover that the records corresponding to each of these constituencies appears in separate database tables.

Your first instinct might be to export the data from each table into a spreadsheet and combine the information there. However, you can combine these records within your database by writing separate queries for each constituency and combining the results of those queries with the UNION statement.

UNION Statement Syntax

The syntax of the UNION statement is quite simple:

SELECT_Query_1
UNION [ALL]
SELECT_Query_2

The two SELECT queries above must meet several requirements:

• They must return the same number of fields.
• The fields must be in the same order.
• The fields must have compatible data types.

The only optional argument with the UNION statement is the addition of the ALL keyword to the command. If you include this keyword, the database will not remove duplicate results from the query output. If you don’t want (or care) to remove duplicate results, you should include this keyword in your command to dramatically improve query performance.

UNION Statements by Example

The easiest way to understand the UNION statement is through the use of an example. Let’s return to that school database described earlier. We can use the following SQL statement to produce a directory containing the names and telephone numbers of all students, faculty and staff:

SELECT first_name, last_name, telephone
FROM students
UNION
SELECT fname, lname, phone
FROM faculty
UNION
SELECT firstname, lastname, telephone
FROM staff

Notice in the example above that the fields do not need to have the same names in each table. As long as the data types are compatible, your database will still be able to combine the results. Additionally, it’s important to point out that the database tables may contain plenty of other information: the UNION statement does not require that the tables be identical in their entirety, only that you include compatible fields in your SELECT statement. Finally, we did not use the ALL keyword in the statement above. Therefore, the database will remove any duplicate results from the output.

That’s all there is to the UNION statement. You can use this SQL command to combine results from multiple tables in your relational databases.