SQL Set Operators: The Complete Guide to UNION, INTERSECT & MINUS
Are you wondering what set operators are?
Have you seen keywords like UNION or MINUS and want to know what they do and why they are used?
In this article, you’ll learn all about set operators such as these, why they are used, and see some examples.
What are SQL Set Operators?
A set operator in SQL is a keyword that lets you combine the results of two queries into a single query.
Sometimes when working with SQL, you’ll have a need to query data from two more tables. But instead of joining these two tables, you’ll need to list the results from both tables in a single result, or in different rows. That’s what set operators do.
The Different Types of Set Operators
There are a few different set operators that can be used, depending on your needs, and which database vendor you’re using.
The different set operators are:
- UNION
- UNION ALL
- MINUS
- INTERSECT
- EXCEPT
Let’s take a look at each of these, using some sample data.
Using Set Operators
Set operators are used like this:
1SELECT your_select_query
2set_operator
3SELECT another_select_query;
It uses two (or more) SELECT queries, with a set operator in the middle.
There are a few things to keep in mind though.
When selecting your columns, the number of columns needs to match between queries, and the data type of each column needs to be compatible.
So, if you select three columns in the first query, you need to select three columns in the second query. The data types also need to be compatible, so if you select a number and two character types in the first query, you need to do the same in the second query.
Also, if you want to order your results, the ORDER BY must go at the end of the last query. You can’t add ORDER BY inside each SELECT query before the set operator.
Sample Data
Our sample data for this article uses a customer and employee table.
Customer
| first_name | last_name |
|---|---|
| Stephen | Jones |
| Mark | Smith |
| Denise | King |
| Paula | Johnson |
| Richard | Archer |
Employee
| first_name | last_name |
|---|---|
| Christina | Jones |
| Michael | McDonald |
| Paula | Johnson |
| Stephen | Jones |
| Richard | Smith |
Each of our query examples in this article will use this data.
UNION: Combining Results
The UNION keyword or set operator will allow you to combine the results of two queries. It removes any duplicate results and shows you the combination of both.
Expressed as a Venn diagram, where each circle represents a query result, it looks like this:
Let’s see an example based on the sample data above. If we wanted to select from both the employee and customer tables, using UNION, our query would look like this:
1SELECT first_name, last_name
2FROM customer
3UNION
4SELECT first_name, last_name
5FROM employee;
Result:
| first_name | last_name |
|---|---|
| Stephen | Jones |
| Mark | Smith |
| Denise | King |
| Paula | Johnson |
| Richard | Archer |
| Christina | Jones |
| Michael | McDonald |
| Richard | Smith |
This shows us all customer and employee records.
But looking at this, we can’t tell which record comes from each table. Often we don’t need to know, but sometimes we do.
How can we do that? We can add a static value to each query to indicate which table it came from, or what type of record it is. As long as the number and type of columns match, the query will work.
1SELECT ‘Customer’ AS record_type, first_name, last_name
2FROM customer
3UNION
4SELECT ‘Employee’, first_name, last_name
5FROM employee;
Result:
| record_type | first_name | last_name |
|---|---|---|
| Customer | Stephen | Jones |
| Customer | Mark | Smith |
| Customer | Denise | King |
| Customer | Paula | Johnson |
| Customer | Richard | Archer |
| Employee | Christina | Jones |
| Employee | Michael | McDonald |
| Employee | Richard | Smith |
In this query, I added in a value of ‘Customer’ to display for all records from the Customer table. It has a column alias of record_type. The same column is labelled as ‘Employee’ from the employee table. Looking at the results you can see which records came from which table.
You don’t need to specify the column aliases on the second table. Oracle will know that the first columns match and use the alias already provided in the first query.
To order the results, the ORDER BY needs to go at the end.
1SELECT ‘Customer’ AS record_type, first_name, last_name
2FROM customer
3UNION
4SELECT ‘Employee’, first_name, last_name
5FROM employee
6ORDER BY record_type, first_name, last_name;
Results:
| record_type | first_name | last_name |
|---|---|---|
| Customer | Denise | King |
| Customer | Mark | Smith |
| Customer | Paula | Johnson |
| Customer | Richard | Archer |
| Customer | Stephen | Jones |
| Employee | Christina | Jones |
| Employee | Michael | McDonald |
| Employee | Richard | Smith |
All of the set operators support standard keywords inside the SELECT statement. The set operator is just a way of combining results.
So, you can add WHERE clauses like this if you need to:
1SELECT ‘Customer’ AS record_type, first_name, last_name
2FROM customer
3WHERE status = ‘Active’
4UNION
5SELECT ‘Employee’, first_name, last_name
6FROM employee
7WHERE emp_status = ‘Current’
8ORDER BY record_type, first_name, last_name;
What’s the Difference Between a UNION and a JOIN?
The UNION and JOIN keywords both combine results from two different tables or queries.
The difference is how they are combined.
UNION combines data into separate rows, and JOIN combines data into separate columns.
When performing a JOIN, there is a column that matches between the two tables, and additional data may be displayed.
If we wanted to JOIN our employee and customer tables, our query might look like this:
1SELECT
2e.emp_id,
3e.first_name,
4e.last_name,
5c.customer_id,
6c.first_name,
7c.last_name
8FROM employee e
9INNER JOIN customer c
10ON e.first_name = c.first_name
11AND e.last_name = c.last_name;
Result:
| cust_id | first_name | last_name | emp_id | first_name | last_name |
|---|---|---|---|---|---|
| 15 | Stephen | Jones | 3 | Stephen | Jones |
| 81 | Paula | Johnson | 9 | Paula | Johnson |
When we use UNION to combine our tables, it looks like this:
1SELECT first_name, last_name
2FROM customer
3UNION
4SELECT first_name, last_name
5FROM employee;
Result:
| first_name | last_name |
|---|---|
| Stephen | Jones |
| Mark | Smith |
| Denise | King |
| Paula | Johnson |
| Richard | Archer |
| Christina | Jones |
| Michael | McDonald |
| Richard | Smith |
We can use different JOIN types to display different records based on matches being found, but in general, that's how a join is different to a UNION.
More Information
For more information on UNION:
UNION ALL: Combining Results in a Different Way
The UNION ALL set operator also combines the results from two queries.
It’s very similar to UNION, but it does not remove duplicates.
Expressed as a Venn diagram, it looks like this:
Let’s see an example. If we wanted to select from both the employee and customer tables, using UNION ALL, our query would look like this:
1SELECT first_name, last_name
2FROM customer
3UNION ALL
4SELECT first_name, last_name
5FROM employee;
Result:
| first_name | last_name |
|---|---|
| Stephen | Jones |
| Mark | Smith |
| Denise | King |
| Paula | Johnson |
| Richard | Archer |
| Christina | Jones |
| Michael | McDonald |
| Paula | Johnson |
| Stephen | Jones |
| Richard | Smith |
This shows us all customer and employee records.
There are some records in this table that appear twice (Stephen Jones and Paula Johnson). Why is that?
This is because UNION ALL does not remove duplicates. So the same first name and last name values exist in both the customer and employee tables, and this query shows each of them.
We can use the same adjustment to the query as we looked at earlier to see which table the data comes from:
1SELECT ‘Customer’ AS record_type, first_name, last_name
2FROM customer
3UNION ALL
4SELECT ‘Employee’, first_name, last_name
5FROM employee;
Result:
| record_type | first_name | last_name |
|---|---|---|
| Customer | Stephen | Jones |
| Customer | Mark | Smith |
| Customer | Denise | King |
| Customer | Paula | Johnson |
| Customer | Richard | Archer |
| Employee | Christina | Jones |
| Employee | Michael | McDonald |
| Employee | Paula | Johnson |
| Employee | Stephen | Jones |
| Employee | Richard | Smith |
This will let us see which table the data comes from, for each record.
What’s the Difference between UNION and UNION ALL?
The major difference between UNION ALL and UNION in SQL is that UNION removes any duplicate results from the final result set, and UNION ALL does not. UNION performs a DISTINCT on the result set to remove duplicates.
To remember this, consider that "ALL" in UNION ALL means "show all records".
Therefore, UNION ALL will almost always show more results, as it does not remove duplicate records.
As a result of this, UNION is often slower than UNION ALL, because there is an operation to remove duplicate values (a.k.a DISTINCT), which is often a costly step in a query.
UNION ALL does not perform a distinct, so is usually faster.
So, if you don't need to have unique rows in your result set, or if you're sure the rows in the database or query are unique already, then use UNION ALL.
More Information
For more information on UNION ALL:
- UNION ALL Optimisation
- SQL SERVER – Puzzle – Why Does UNION ALL Work but UNION Produces Error?
- Is there any limit on the number of UNION ALL statements in a single SQL query on Oracle database?
MINUS or EXCEPT: Finding Results That Are Missing
Another set operator we can use is the MINUS keyword or the EXCEPT keyword.
The MINUS set operator will return results that are found in the first query specified that don’t exist in the second query.
The EXCEPT keyword is similar to MINUS, but is available in SQL Server and other databases.
As a Venn diagram, it looks like this:
Using our example data, we could use the MINUS set operator to find all names in the customer table that don’t exist in the employee table.
Our query would look like this:
1SELECT first_name, last_name
2FROM customer
3MINUS
4SELECT first_name, last_name
5FROM employee;
Result:
| first_name | last_name |
|---|---|
| Mark | Smith |
| Denise | King |
| Richard | Archer |
If a result exists in the employee table as well as the customer table, it is not shown. Only the results from the customer table that are not in the employee table are shown.
What About the EXCEPT Set Operator?
The EXCEPT keyword is another set operator you might see in your code or in online examples.
EXCEPT is the same as MINUS - they both show results from one query that don’t exist in another query.
However, MINUS is an Oracle-specific keyword, and EXCEPT is in other databases such as SQL Server.
So, if you see EXCEPT anywhere, just know it’s the same as MINUS but for a different database.
More Information
For more information on MINUS and EXCEPT:
- Minus operator in SQL
- SQL Server: EXCEPT and INTERSECT
- SQL Server equivalent of Oracle''s "MINUS" operator?
INTERSECT: Showing Common Results
The INTERSECT keyword allows you to find results that exist in both queries. Two SELECT statements are needed, and any results that are found in both of them are returned if INTERSECT is used.
As a Venn diagram, it looks like this:
Using our example data, we could use the INTERSECT set operator to find all names in the customer table that don’t exist in the employee table.
Our query would look like this:
1SELECT first_name, last_name
2FROM customer
3INTERSECT
4SELECT first_name, last_name
5FROM employee;
Result:
| first_name | last_name |
|---|---|
| Stephen | Jones |
| Paula | Johnson |
This shows all names that are in both the customer and employee table.
If we want to order these, we add an ORDER BY at the end.
1SELECT first_name, last_name
2FROM customer
3INTERSECT
4SELECT first_name, last_name
5FROM employee
6ORDER BY first_name, last_name;
Result:
| first_name | last_name |
|---|---|
| Paula | Johnson |
| Stephen | Jones |
What’s the Difference between UNION and INTERSECT?
The difference between UNION and INTERSECT is that UNION gets results from both queries and combines them, while INTERSECT gets results that only exist in both queries.
So, if Query 1 returns records A and B, and Query 2 returns records B and C, UNION would return A, B and C. INTERSECT would only return B.
More Information
For more information on the INTERSECT set operator:
- Oracle Documentation
- SQL Server Documentation
- You Probably don’t Use SQL INTERSECT or EXCEPT Often Enough
Working with Two or More Set Operators
So far we’ve looked at combining two queries into one using one set operator.
What if you want to combine three queries using two set operators? Or more?
You can do that in SQL.
You can just add another set operator and another query at the end.
For example, let’s say that along with our customer and employee tables, we had a table for referrers (people who referred customers).
If we wanted to see all of the names in all tables, we could write a query like this:
1SELECT first_name, last_name
2FROM customer
3UNION
4SELECT first_name, last_name
5FROM employee
6UNION
7SELECT first_name, last_name
8FROM referrer
9ORDER BY first_name, last_name;
This query will select from the customer table, UNION that to the employee table, and then union the results at that point to the referrer table.
If we wanted to see all records in the customer and employee that weren’t in the referrer table, we can write this query:
1SELECT first_name, last_name
2FROM customer
3UNION ALL
4SELECT first_name, last_name
5FROM employee
6MINUS
7SELECT first_name, last_name
8FROM referrer
9ORDER BY first_name, last_name;
Now, you might be wondering how this works? The first query is run, then a UNION ALL is performed with the second query, then using those results a MINUS is performed with the third query.
Unlike the mathematical operators (such as addition and multiplication), there is no order of operations with set operators. They are treated equally and run in order in the query from start to finish.
However, you can use brackets to force an order of operations with set operators. Let’s say you wanted to find all customer names, as well as employee names that weren’t referrer names.
Customer + (Employee - Referrer)
The query would look like this:
1SELECT first_name, last_name
2FROM customer
3UNION ALL
4(SELECT first_name, last_name
5FROM employee
6MINUS
7SELECT first_name, last_name
8FROM referrer)
9ORDER BY first_name, last_name;
Adding the brackets around the second and third SELECT queries will ensure those queries are run together first, and the results of that are used to UNION ALL to the first select.
Using different combinations of brackets and set operators can give you the results you need when working with multiple queries.
Conclusion
Set operators can be useful when you want to combine the results of different queries into different rows. In SQL, the different set operators are UNION, UNION ALL, INTERSECT and MINUS (or EXCEPT, depending on your database).
Do you have any questions about set operators? Let me know in the comments below.