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.

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

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 combine 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:

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:

Note:

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