Online College Courses for Credit

+
UNION to Combine Results

UNION to Combine Results

Rating:
(0)
Author: Sophia Tutorial
Description:

Given 3 separate but related queries, modify the queries to use UNION in order to combine the 3 result sets into a single result set.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores the UNION operator to combine result sets of various queries.

The UNION operator allows us to combine the result sets from two or more different SELECT statements into a single result set while excluding duplicate rows. The individual SELECT statements will not have any difference other than the first SELECT statement will not have a semi-colon at the end of it. For example:

SELECT <columnlist>
FROM <table1>
SELECT <columnlist>
FROM <table2>;

Let us take a look at an example in which our organization may want to send all users (customers and employees) an email to inform them of an upcoming sale. The same message would be sent to all individuals. Instead of querying the customer and employee table separately and having two result sets to merge together, we can pull their name and email into a single result set:

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

File:11451-2340-1.png

Note that this will not distinguish which individual is a customer or employee. We could add an extra string in the column list to distinguish the difference:

SELECT first_name, last_name, email, 'Employee'
FROM employee
UNION
SELECT first_name, last_name, email, 'Customer'
FROM customer;

File:11452-2340-2.png

Note that your result set may look different in this case as the 4th column was sorted by the type in descending order.

For us to use the UNION operator, the tables that we are querying from should have the same attribute characteristics meaning that the number of columns and types of data between the two SELECT statements should match.

SELECT customer_id
FROM invoice
UNION
SELECT first_name
FROM customer;

In the example above, the customer_id is an integer while the first_name is a character string. As such, the data types don’t match and we get the following error:

File:11453-2340-3.png

If we have a different number of columns in each of the SELECT statements, we would run into a separate error:

SELECT customer_id
FROM invoice
UNION
SELECT customer_id, first_name
FROM customer;

File:11454-2340-4.png

We could create a UNION for more than 2 queries as well. For example, we may want to look at all of the countries that we operate in. As such, we would need to look at the customer table, invoice, and employee table.

SELECT billing_country
FROM invoice
UNION
SELECT country
FROM customer
UNION
SELECT country
FROM employee;

Notice that the result set that returns only has 24 rows as it excludes all duplicate values:

File:11455-2340-5.png

If we wanted to retain the duplicate values, we would use UNION ALL instead of UNION:

SELECT billing_country
FROM invoice
UNION ALL
SELECT country
FROM customer
UNION ALL
SELECT country
FROM employee;

File:11456-2340-6.png

You should see now that the duplicates are included, we have 479 results returned with the duplicates listed. Note as well that the column name that is used is the output reflects the first SELECT statement in the list. For example, if we swapped the second for the first SELECT statement:

SELECT country
FROM customer
UNION ALL
SELECT billing_country
FROM invoice
UNION ALL
SELECT country
FROM employee;

File:11457-2340-7.png

As such, if we wanted to use aliases to rename a column, we would only need to do so for the first SELECT statement:

SELECT first_name as "First Name", last_name as "Last Name", email as "Email", 'Employee' as "Type"
FROM employee
UNION
SELECT first_name, last_name, email, 'Customer'
FROM customer;

File:11458-2340-8.png


try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
The UNION operator allows result sets to be combined together into a single result set.