Movatterモバイル変換

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:

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 withUNION andEXCEPT, if neitherDISTINCT norALL is specified, the default isDISTINCT.

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 withUNION, the operands must have the same number of columns. Result set column types are also determined as forUNION.

INTERSECT has greater precedence than and is evaluated beforeUNION andEXCEPT, 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);

ForINTERSECT ALL, the maximum supported number of duplicates of any unique row in the left hand table is4294967295.