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.

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

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:

1SELECT * FROM Table1
2UNION
3SELECT * FROM Table2;

The resulting table would look like this:

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.

1SELECT * FROM Table1
2UNION ALL
3SELECT * 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.

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:

1SELECT * FROM Table1
2INTERSECT
3SELECT * 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:

1SELECT * FROM Table1
2MINUS
3SELECT * 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.

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.
• 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.
• 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.