Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 43.3Mb
PDF (A4) - 43.4Mb
Man Pages (TGZ) - 296.6Kb
Man Pages (Zip) - 401.9Kb
Info (Gzip) - 4.3Mb
Info (Zip) - 4.3Mb
Excerpts from this Manual

15.2.8 INTERSECT Clause

Press CTRL+C to copy
query_expression_body INTERSECT [ALL | DISTINCT] query_expression_body [INTERSECT [ALL | DISTINCT] query_expression_body] [...] query_expression_body: See Section 15.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:

Press CTRL+C to copy
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:

Press CTRL+C to copy
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:

Press CTRL+C to copy
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.