MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

INTERSECT [ALL | DISTINCT]`query_expression_body`

[INTERSECT [ALL | DISTINCT]`query_expression_body`

] [...]`query_expression_body`

:`query_expression_body`

See Section 13.2.14, “Set Operations with UNION, INTERSECT, and EXCEPT”

`INTERSECT`

limits the result from multiple query
blocks to those rows which are common to all. Example:

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

`TABLE`

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

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

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

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

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>+------+------+ | m | n | +------+------+ | 1 | 3 | | 3 | 4 | +------+------+ 2 rows in set (0.00 sec) mysql>`TABLE c INTERSECT DISTINCT TABLE c;`

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

(`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.