Set operators, such as UNION, UNION ALL, EXCEPT or MINUS, INTERSECT, are commonly used in SQL queries. They combine the results of 2 or more queries into a single result set.

The pre-requisite to use SET OPERATORS is that the number of columns must be the same between queries, and the data type of each column must be compatible. That is, the datasets must have identical table/column structures.

MySQL does not support EXCEPT or MINUS, INTERSECT, only supports UNION and UNION ALL, see practice here, but we'll show you how to achieve the same effect of EXCEPT/MINUS and INTERSECT in MySQL by using JOINs.

1. UNION ALL

UNION ALL combines all rows from the results of 2 or more queries, including all duplicates.

UNION ALL simply vertically appends the result of the first query to the result of the second query. The only checking the database engine does is to make sure the data structures are the same and data types are compatible.

select * from Table1
UNION ALL
select * from Table2

2. UNION

UNION combines the distinct rows from the results of 2 or more queries.

UNION appends 1 dataset to another vertically. In addition, if duplicate rows exist in both datasets, it will only include the distinct rows into the result set.

select * from Table1
UNION
select * from Table2

3. MINUS/EXCEPT

MINUS/EXCEPT only returns distinct rows that only exists in the result of the first query.

Oracle supports MINUS and SQL Server uses EXCEPT to do the same thing. Both MINUS and EXCEPT have the same effect. MySQL does not support either, but the same effect can be achieved by using outer join.

select * from Table1
MINUS
select * from Table2

select * from Table1
EXCEPT
select * from Table2

4. INTERSECT

INTERSECT returns distinct rows that exist in the results of both queries.

MySQL does not support INTERSECT operator, but the same effect can be achieved by using inner join.

select * from Table1
INTERSECT
select * from Table2

Other tutorials in this category

1. Using Inner Joins to Combine Data from Two Tables

2. Using Outer Joins to Combine Data from Two Tables

3. Another Example for Outer Joins (three tables)

4. Using Self Joins to Combine Data from the Same Table

5. Using UNION to Append Result Sets

6. Create working tables to demonstrate how to mimic set operators MINUS, EXCEPT, INTERSECT in MySQL

7. How to do MINUS/EXCEPT and INTERSECT in MySQL

8. How to Simulate Full Join in MySQL - Part 1: return both matched and unmatched rows

9. How to Simulate Full Join in MySQL - Part 2: return unmatched rows only

10. How to Simulate Full Join in MySQL - Part 3: use UNION to simulate FULL JOIN

11. Use Cross Join to Combine Data in MySQL

12. Differences Between Join and Union

Back to Tutorial Index Page