Back to: MySQL Tutorials for Beginners and Professionals
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:
- UNION: It is used to combine two or more result sets into a single set, without duplicates.
- UNION ALL: It is used to combine two or more result sets into a single set, including duplicates.
- INTERSECT: It is used to combine two result sets and returns the data which are common in both the result set.
- 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:
- Every SELECT statement involved in the query must have a similar number of columns.
- The columns in the SELECT statement must be in the same order and have similar data types.
- 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.