MySQL: INTERSECT Operator

This tutorial explains how to use the INTERSECT operator with syntax and examples.

Description

Although there is no INTERSECT operator in MySQL, you can easily simulate this type of query using either the IN clause or the EXISTS clause, depending on the complexity of the INTERSECT query.

First, let's explain what an INTERSECT query is. An INTERSECT query returns the intersection of 2 or more datasets. If a record exists in both data sets, it will be included in the INTERSECT results. However, if a record exists in one data set and not in the other, it will be omitted from the INTERSECT results.

Intersect Query

Explanation: The INTERSECT query will return the records in the blue shaded area. These are the records that exist in both Dataset1 and Dataset2.

Syntax

The syntax for the INTERSECT operator in MySQL is:

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
INTERSECT
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

Parameters or Arguments

expression1, expression2, ... expression_n

The columns or calculations that you wish to retrieve.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. The conditions that must be met for the records to be selected.

Note

• There must be same number of expressions in both SELECT statements and have similar data types.

Example - With Single Expression

First, let's explore how to simulate an INTERSECT query in MySQL that has one field with the same data type.

If the database supported the INTERSECT operator (which MySQL does not), this is how you would have use the INTERSECT operator to return the common category_id values between the products and inventory tables.

SELECT category_id
FROM products
INTERSECT
SELECT category_id
FROM inventory;

Since you can't use the INTERSECT operator in MySQL, you will use the IN operator to simulate the INTERSECT query as follows:

SELECT products.category_id
FROM products
WHERE products.category_id IN (SELECT inventory.category_id FROM inventory);

In this simple example, you can use the IN operator to return all category_id values that exist in both the products and inventory tables.

Now, let's complicate our example further by adding WHERE conditions to the INTERSECT query.

For example, this is how the INTERSECT would look with WHERE conditions:

SELECT category_id
FROM products
WHERE category_id  100
INTERSECT
SELECT category_id
FROM inventory
WHERE quantity > 0;

This is how you would simulate the INTERSECT query using the IN operator and include the WHERE conditions:

SELECT products.category_id
FROM products
WHERE products.category_id  100
AND products.category_id IN
   (SELECT inventory.category_id
    FROM inventory
    WHERE inventory.quantity > 0);

In this example, the WHERE clauses have been added that filter both the products table as well as the results from the inventory table.

Example - With Multiple Expressions

Next, let's look at how to simulate an INTERSECT query in MySQL that returns more than one column.

First, this is how you would use the INTERSECT operator to return multiple expressions.

SELECT contact_id, last_name, first_name
FROM contacts
WHERE contact_id  100
INTERSECT
SELECT customer_id, last_name, first_name
FROM customers
WHERE last_name > 'Johnson';

Again, since you can't use the INTERSECT operator in MySQL, you can use the EXISTS clause in more complex situations to simulate the INTERSECT query as follows:

SELECT contacts.contact_id, contacts.last_name, contacts.first_name
FROM contacts
WHERE contacts.contact_id  100
AND EXISTS (SELECT *
            FROM customers
            WHERE customers.last_name > 'Johnson'
            AND customers.customer_id = contacts.contact_id
            AND customers.last_name = contacts.last_name
            AND customers.first_name = contacts.first_name);

In this more complex example, you can use the EXISTS clause to return multiple expressions that exist in both the contacts table where the contact_id is less than 100 as well as the customers table where the last_name is not equal to Johnson.

Because you are doing an INTERSECT, you need to join the intersect fields as follows:

AND customers.customer_id = contacts.contact_id
AND customers.last_name = contacts.last_name
AND customers.first_name = contacts.first_name

This join is performed to ensure that the customer_id, last_name, and first_name fields from the customers table are intersected with the contact_id, last_name, and first_name fields from the contacts table.