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

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.

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

The syntax for the INTERSECT operator in MySQL is:

- expression1, expression2, ... expression_n
- tables
- WHERE conditions

The columns or calculations that you wish to retrieve.

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

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

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

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.

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

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:

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

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

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.

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:

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:

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.