SQL Set Operators

Introduction

The SET operators are a special kind of operators which serve the purpose of combining the results of two or more component queries. Queries that contain set operators are referred to as compound queries. The result of these operations is a single set, no matter how many SELECT statements will be present in the compound query. The four SET operators which are very frequently used and will be covered in this tutorial are UNION, UNION ALL, INTERSECT, and MINUS.

There exist several rules that have to be followed when using such SET Operators:

• The number of columns in each query's result set must be the same.
• The data types in each query’s result set must be compatible, respectively.
• The last statement may include an ORDER BY clause in case we'd like to sort the results.
• The first select statement must identify the column names or aliases.

Throughout this tutorial, we will be working with the tables shown below, named Table1 and Table2, respectively:

Column Matching

Before any SET operator can combine two result sets, the two SELECT statements have to line up correctly. The database compares the columns by position, not by the column names.

That means the first column in the first SELECT should be compatible with the first column in the second SELECT, the second with the second, and so on.

SELECT ID, Name, City
FROM Table1
UNION
SELECT ID, Name, City
FROM Table2;

This works because both queries return three compatible columns. However, the following query would not be valid because the number of columns is different:

SELECT ID, Name, City
FROM Table1
UNION
SELECT ID, Name
FROM Table2;

Also, the final result will usually use the column names from the first SELECT statement.

Which pair of SELECT statements can be combined most safely with a SET operator?

UNION

The results of two or more SELECT operations can be combined using the UNION set operator. This operator works in the same manner as Distinct. That means that after the UNION operation is performed, any possible duplicate rows will be eliminated. This is actually the main difference between UNION and UNION ALL.

Let us see the UNION operation in action by performing this operation on the two tables shown above. The query we’d have to run in order to do this is:

SELECT * FROM Table1
UNION
SELECT * FROM Table2;

The resulting table would look like this:

As we can see, even though the rows with customer IDs 2 and 3 were present in both Table1 and Table2, they have been removed since they are completely identical and the UNION operation returns rows with distinct values (for at least one field).

UNION ALL

As mentioned above, UNION and UNION ALL are pretty similar, with the main difference being in how they treat any possible duplicate values in the resulting set. That means that when using the UNION ALL operator, all duplicate values are included in the final result.

In order to better see the difference between these two operators, let us take a look at the query and resulting table shown below.

SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2;

Result:

In this example, we can see that the rows with customer IDs 2 and 3 are contained twice, which means that the UNION ALL operator accepts duplicates in its final result.

The main difference between UNION and UNION ALL is that UNION contains duplicate rows in the resulting table, while UNION ALL removes the duplicate rows.

ORDER BY

When using SET operators, the ORDER BY clause should be written only once, at the very end of the compound query. It sorts the final combined result, not just one of the SELECT statements.

SELECT ID, Name, City
FROM Table1
UNION
SELECT ID, Name, City
FROM Table2
ORDER BY Name;

A common mistake is trying to place ORDER BY between the two SELECT statements. Instead, first combine the result sets, and then sort the result that comes out of the SET operation.

In many databases, the final column names are taken from the first SELECT statement, so it is usually safest to order by a column from the first SELECT list.

INTERSECT

The INTERSECT set operator is also used to combine two or more SELECT statements, but the resulting table only holds the rows which exist in all SELECT statements. In other words, it only returns the duplicate rows, but they are present only once in the final result.

Let’s see this operator in action by running the following query on our example tables:

SELECT * FROM Table1
INTERSECT
SELECT * FROM Table2;

The resulting table looks like this:

As we can see, the resulting table shows only the customers with IDs 2 and 3, which are the only two entries present in both tables.

MINUS

The MINUS set operator shows the rows that are present in the first query but absent in the second in ascending order, removing any duplicate rows.

So, for example, if we want to get the entries from Table1, but leave out those entries that are also present in Table2, we’d run the following query:

SELECT * FROM Table1
MINUS
SELECT * FROM Table2;

Then, the resulting table would look like this:

As we can see, the resulting table is the same as Table1, only without the customers with IDs 2 and 3, which are the ones that are present in Table2 as well.

We can use the MINUS operator to get the rows present in Table2 that are not present in Table1 as well.

One Pager Cheat Sheet

• This tutorial covers four SET operators - UNION, UNION ALL, INTERSECT and MINUS - which combine the results of two or more component queries to form a single set of results, while adhering to certain rules.
• - SET operators match columns by position, and each SELECT must return the same number of compatible columns.
• - Column count and compatible data types matter more than matching column names.
• The UNION set operator combines the results of two or more SELECT operations, removing any duplicate rows during the process.
• The UNION ALL operator allows for duplicate values to be included in the resulting set, as seen in the sample query and resulting table provided.
• UNION removes duplicate rows from the resulting table, while UNION ALL includes them.
• - In a compound query, ORDER BY belongs at the end and sorts the final combined result.
• The INTERSECT set operator is used to combine two or more SELECT statements, returning only the duplicate rows which are present in each SELECT statement.
• The MINUS set operator returns the rows from the first query that are not present in the second query, removing any duplicate rows and displaying the result in ascending order.
• The MINUS operator can be used to get the rows of Table2 that are not present in Table1 by reversing the order of the two queries.