Back to cookbooks list Articles Cookbook

How to Combine the Results of Two Queries in SQL

Problem:

You’d like to display data from given columns (of a similar data type) from two tables in SQL.

Example:

There are two tables in our database: employee and customer.

The employee table contains data in the following columns: id, first_name, last_name, and age.

idfirst_namelast_nameage
1TomMiller22
2JohnSmith26
3LisaWilliams30
4CharlesDavis21
5JamesMoore22

The customer table contains data in the following columns: id, first_name, last_name, and age.

idfirst_namelast_nameage
1MilanSmith45
2CharlesDavis21
3MarkBacker19

In one result set, let’s display the first name, last name, and age for all people in the database, both employees and customers.

Solution 1:

We’ll use UNION ALL to join data from columns in two tables.

Here’s the query you’d write:

SELECT first_name, last_name, age FROM employee
UNION ALL
SELECT first_name, last_name, age FROM customer;

Here’s the result:

first_namelast_nameage
TomMiller22
JohnSmith26
LisaWilliams30
CharlesDavis21
JamesMoore28
MilanSmith45
CharlesDavis21
MarkBacker19

Discussion:

Use the UNION ALL clause to join data from columns in two or more tables. In our example, we join data from the employee and customer tables. On the left of the UNION ALL keyword, put the first SELECT statement to get data from the first table (in our example, the table employee). On the right, use another SELECT statement to get data from the second table (in our example, customer).

Remember that the selected data in both tables must be of the same data type in each column. For example, if the first column in the first SELECT is a string data type, the first column in the second SELECT must also be a string data type. If the second column in the first SELECT statement is an integer, the second column in the second table must also be an integer type.

In the first query, we selected age (the age of the employee, which is an integer data type) for the third column. Therefore, the third column in the second SELECT is also an integer value; it’s the age of the customer.

The second columns in both SELECT statements are the same data type. However, if the values are the same in both tables, they will be displayed multiple times; for example, ‘Charles Davis 21’ is shown twice in the result set.

What if don’t want multiple identical records in the result table? In this case, use UNION. It is similar to UNION ALL, but it removes duplicate records. Look at the following example.

Solution 2:

Here’s the query that avoids duplicate records:

SELECT first_name, last_name FROM employee
UNION 
SELECT first_name, last_name FROM customer;

Here’s the result of the above query:

first_namelast_name
MarkBacker
JamesMoore
JohnSmith
CharlesDavis
MilanSmith
TomMiller
LisaWilliams

Note:

UNION ALL is faster than UNION, but UNION removes duplicate rows. The choice depends on the result data we need.

Recommended courses:

Recommended articles:

See also: