SET Operators (UNION, UNION ALL, INTERSECT, & EXCEPT) in MySQL

SET Operators in MySQL (UNION, UNION ALL, INTERSECT, & EXCEPT)

In this article, I am going to discuss SET Operators (UNION, UNION ALL, INTERSECT, & EXCEPT) in MySQL with Examples. Please read our previous article where we discussed Concatenation and Temporal Operators in MySQL with examples.

What are SET Operators in MySQL?

The SET Operators in MySQL are basically used to combine the result of more than 1 select statement and return the output as a single result set. In SQL, 4 types of set operators are. They are as follows:

  1. UNION: It is used to combine two or more result sets into a single set, without duplicates.
  2. UNION ALL: It is used to combine two or more result sets into a single set, including duplicates.
  3. INTERSECT: It is used to combine two result sets and returns the data which are common in both the result set.
  4. EXCEPT: It is used to combine two result sets and returns the data from the first result set which is not present in the second result set.
Points to Remember while working with Set Operations:
  1. Every SELECT statement involved in the query must have a similar number of columns.
  2. The columns in the SELECT statement must be in the same order and have similar data types.
  3. In order to sort the result, an ORDER BY clause should be part of the last select statement. The column names or aliases must be found out by the first select statement.
Examples to understand SET Operators in MySQL:

We are going to use the following EmployeeUK and EmployeeUSA tables to understand the SET Operators in MySQL.

Examples to understand SET Operators in MySQL

Please use the below SQL Script to create the database EmployeeDB and the tables EmployeeUK and EmployeeUSA with the required data.

CREATE DATABASE EmployeeDB;
USE EmployeeDB;

CREATE TABLE EmployeeUK
(
  EmployeeId INT PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  Gender VARCHAR(10),
  Department VARCHAR(20)
);

INSERT INTO EmployeeUK VALUES(1, 'Pranaya', 'Rout', 'Male','IT');
INSERT INTO EmployeeUK VALUES(2, 'Priyanka', 'Dewangan', 'Female','IT');
INSERT INTO EmployeeUK VALUES(3, 'Preety', 'Tiwary', 'Female','HR');
INSERT INTO EmployeeUK VALUES(4, 'Subrat', 'Sahoo', 'Male','HR');
INSERT INTO EmployeeUK VALUES(5, 'Anurag', 'Mohanty', 'Male','IT');
INSERT INTO EmployeeUK VALUES(6, 'Rajesh', 'Pradhan', 'Male','HR');
INSERT INTO EmployeeUK VALUES(7, 'Hina', 'Sharma', 'Female','IT');

CREATE TABLE EmployeeUSA
(
  EmployeeId INT PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  Gender VARCHAR(10),
  Department VARCHAR(20)
);

INSERT INTO EmployeeUSA VALUES(1, 'James', 'Pattrick', 'Male','IT');
INSERT INTO EmployeeUSA VALUES(2, 'Priyanka', 'Dewangan', 'Female','IT');
INSERT INTO EmployeeUSA VALUES(3, 'Sara', 'Taylor', 'Female','HR');
INSERT INTO EmployeeUSA VALUES(4, 'Subrat', 'Sahoo', 'Male','HR');
INSERT INTO EmployeeUSA VALUES(5, 'Sushanta', 'Jena', 'Male','HR');
INSERT INTO EmployeeUSA VALUES(6, 'Mahesh', 'Sindhey', 'Female','HR');
INSERT INTO EmployeeUSA VALUES(7, 'Hina', 'Sharma', 'Female','IT');
UNION Operator in MySQL

The UNION operator is used to combine the result set of two or more SELECT statements into a single result set by removing the duplicate records. That means the UNION Operator selects only the distinct values. Following is the Syntax to use UNION Operator in MySQL.

UNION Operator in MySQL

MySQL UNION Operator Example:

The following query combines two select statements using the UNION operator. In our example, both the EmployeeUK and EmployeeUSA tables having seven records.

SELECT FirstName, LastName, Gender, Department FROM EmployeeUK
UNION
SELECT FirstName, LastName, Gender, Department FROM EmployeeUSA;

Once you execute the above query, you will get the following result set. Please observe, here we don’t have any duplicate data. Here, in the result set, we got a total of 11 rows out of 14 rows. This is because 3 rows are present in both the result set.

MySQL UNION Operator Example

UNION ALL Operator in MySQL

The UNION ALL operator is used to combine the result set of two or more SELECT statements into a single result including the duplicate values. Following is the Syntax to use UNION ALL Operator in MySQL.

UNION ALL Operator in MySQL

MySQL UNION ALL Operator Example:

The following query combines two select statements using the UNION ALL operator

SELECT FirstName, LastName, Gender, Department FROM EmployeeUK
UNION ALL
SELECT FirstName, LastName, Gender, Department FROM EmployeeUSA;

Once you execute the above query, you will get the following result set. Please observe, here we got all the 14 rows in the result set.

MySQL UNION ALL Operator Example

Differences between UNION and UNION ALL Operator in MySQL

From the output, it is very clear to us that UNION Operator removes duplicate rows whereas UNION ALL operator does not remove the duplicate rows. When we use a UNION operator, in order to remove the duplicate rows from the result set, it has to do a distinct operation which is time-consuming. For this reason, UNION ALL is much faster than UNION Operator in MySQL. 

UNION/UNION ALL with ORDER BY Clause in MySQL

The UNION/UNION ALL Operator can be used with the ORDER BY clause to sort the result returned from the query. Suppose we want to sort the employees by First Name column values. ORDER BY clause should be part of the last select statement. The SQL statement will be:

SELECT FirstName, LastName, Gender, Department FROM EmployeeUK
UNION
SELECT FirstName, LastName, Gender, Department FROM EmployeeUSA
ORDER BY FirstName;

Now execute the query and you should get the following output.

UNION/UNION ALL with ORDER BY Clause in MySQL

Here you can see the employees are sorted according to their FirstName column values.

MySQL EXCEPT Operator:

The EXCEPT operator is used to combine two tables or two result sets and will return rows from the first select statement that are not present in the second select statement. Following is the syntax of EXCEPT Operator.

EXCEPT Operator in MySQL

But, the EXCEPT Operator is not supported by MYSQL. We can achieve the EXCEPT Operator functionality in MySQL using the following ways.

Using NOT IN Operator to achieve EXCEPT functionality:

Here, we are checking the FirstName column value only. Following is the SQL Query using the NOT IN Operator which returns the employees from the first EmployeeUK table that are not present in the EmployeeUSA table.

SELECT * FROM EmployeeUK
WHERE FirstName NOT IN (SELECT FirstName FROM EmployeeUSA);

Once you execute the above query, you will get the following result set.

MySQL EXCEPT Operator

Using Join to achieve EXCEPT functionality in MySQL:

We can use LEFT JOIN to achieve the functionality of EXCEPT Operator. Here, the join clause needs to contain all 4 columns FirstName, LastName, Gender, and Department. The where clause picks null values in EmployeeId in EmployeeUSA, which limits to rows that exist in EmployeeUK only.

SELECT t1.* FROM EmployeeUK AS t1
LEFT JOIN EmployeeUSA AS t2 ON 
    t1.FirstName=t2.FirstName
    AND t1.LastName=t2.LastName
    AND t1.Gender=t2.Gender
    AND t1.Department=t2.Department
WHERE t2.EmployeeId IS NULL;

Once you execute the above join query, you will get the following result set.

Using Join to achieve EXCEPT functionality in MySQL

INTERSECT Operator in MySQL

The INTERSECT operator is used to combine two result sets and returns the data which are common in both the result set. Following is the syntax of INTERSECT operator.

SET Operators in MySQL with Examples

But the INTERSECT Operator is not supported by MYSQL. We can achieve the INTERSECT Operator functionality in MySQL using the following ways.

Using IN Operator to achieve INTERSECT functionality:

Here, we are checking the FirstName column value only. Following is the SQL Query using the IN Operator which returns the common employees i.e. the employees which are present in both t EmployeeUK and EmployeeUSA tables. Here, we are checking common based on the First Name column value.

SELECT * FROM EmployeeUK
WHERE FirstName IN (SELECT FirstName FROM EmployeeUSA);

Once you execute the above query, you will get the following result set.

SET Operators in MySQL with Examples

Using Join to achieve INTERSECT functionality in MySQL:

We can use INNER JOIN to achieve the functionality of INTERSECT Operator. Here, the join clause needs to contain all 4 columns FirstName, LastName, Gender, and Department.

SELECT t1.* FROM EmployeeUK AS t1
INNER JOIN EmployeeUSA AS t2 ON 
    t1.FirstName=t2.FirstName
    AND t1.LastName=t2.LastName
    AND t1.Gender=t2.Gender
    AND t1.Department=t2.Department; 

Once you execute the above join query, you will get the following result set.

SET Operators in MySQL with Examples

In the next article, I am going to discuss Assignment Operator in MySQL with Examples. Here, in this article, I try to explain SET Operators in MySQL with Examples. I hope you enjoy this SET Operators in MySQL article.

1 thought on “SET Operators (UNION, UNION ALL, INTERSECT, & EXCEPT) in MySQL”

  1. It would be good to discuss performance differences on these different queries especially as the table sizes grow and how this may depend on indexing of the tables. Without that people may not realise that the query performance may vary significantly and thus one form might be preferred over another.

Leave a Reply

Your email address will not be published. Required fields are marked *