Syntax:
SELECT ...
INTERSECT [ALL | DISTINCT] SELECT ...
[INTERSECT [ALL | DISTINCT] SELECT ...]

INTERSECT limits the result from multiple SELECT statements to those
rows which are common to all. Example:

mysql> TABLE a;
+------+------+
| m    | n    |
+------+------+
|    1 |    2 |
|    2 |    3 |
|    3 |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql> TABLE b;
+------+------+
| m    | n    |
+------+------+
|    1 |    2 |
|    1 |    3 |
|    3 |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql> TABLE c;
+------+------+
| m    | n    |
+------+------+
|    1 |    3 |
|    1 |    3 |
|    3 |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql> TABLE a INTERSECT TABLE b;
+------+------+
| m    | n    |
+------+------+
|    1 |    2 |
|    3 |    4 |
+------+------+
2 rows in set (0.00 sec)

mysql> TABLE a INTERSECT TABLE c;
+------+------+
| m    | n    |
+------+------+
|    3 |    4 |
+------+------+
1 row in set (0.00 sec)

As with UNION and EXCEPT, if neither DISTINCT nor ALL is specified, the
default is DISTINCT.

DISTINCT can remove duplicates from either side of the intersection, as
shown here:

mysql> TABLE c INTERSECT DISTINCT TABLE c;
+------+------+
| m    | n    |
+------+------+
|    1 |    3 |
|    3 |    4 |
+------+------+
2 rows in set (0.00 sec)

mysql> TABLE c INTERSECT ALL TABLE c;
+------+------+
| m    | n    |
+------+------+
|    1 |    3 |
|    1 |    3 |
|    3 |    4 |
+------+------+
3 rows in set (0.00 sec)

(TABLE c INTERSECT TABLE c is the equivalent of the first of the two
statements just shown.)

As with UNION, the operands must have the same number of columns.
Result set column types are also determined as for UNION.

INTERSECT has greater precedence than and is evaluated before UNION and
EXCEPT, so that the two statements shown here are equivalent:

TABLE r EXCEPT TABLE s INTERSECT TABLE t;

TABLE r EXCEPT (TABLE s INTERSECT TABLE t);

For INTERSECT ALL, the maximum supported number of duplicates of any
unique row in the left hand table is 4294967295.

INTERSECT was added in MySQL 8.0.31.

URL: https://dev.mysql.com/doc/refman/8.0/en/intersect.html

