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:
Parameters or Arguments
expression1, expression2, ... expression_n
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.
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.
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.
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.
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.