SQL set operations combine the results of multiple query blocks
into a single result. A query block,
sometimes also known as a simple table, is
any SQL statement that returns a result set, such as
SELECT. MySQL 8.0 (8.0.19 and
later) also supports
VALUES statements. See the
individual descriptions of these statements elsewhere in this
chapter for additional information.
The SQL standard defines the following three set operations:
UNION: Combine all results from two query blocks into a single result, omitting any duplicates.
INTERSECT: Combine only those rows which the results of two query blocks have in common, omitting any duplicates.
EXCEPT: For two query blocks
B, return all results from
Awhich are not also present in
B, omitting any duplicates.
(Some database systems, such as Oracle, use
MINUSfor the name of this operator. This is not supported in MySQL.)
MySQL has long supported
UNION; MySQL 8.0 adds
EXCEPT (MySQL 8.0.31 and later).
Each of these set operators supports an
modifier. When the
ALL keyword follows a set
operator, this causes duplicates to be included in the result. See
the following sections covering the individual operators for more
information and examples.
All three set operators also support a
keyword, which suppresses duplicates in the result. Since this is
the default behavior for set operators, it is usually not
necessary to specify
In general, query blocks and set operations can be combined in any number and order. A greatly simplified representation is shown here:
query_block [set_op query_block] [set_op query_block] ... query_block: SELECT | TABLE | VALUES set_op: UNION | INTERSECT | EXCEPT
This can be represented more accurately, and in greater detail, like this:
query_expression: [with_clause] /* WITH clause */ query_expression_body [order_by_clause] [limit_clause] [into_clause] query_expression_body: query_term | query_expression_body UNION [ALL | DISTINCT] query_term | query_expression_body EXCEPT [ALL | DISTINCT] query_term query_term: query_primary | query_term INTERSECT [ALL | DISTINCT] query_block query_primary: query_block | '(' query_expression_body [order_by_clause] [limit_clause] [into_clause] ')' query_block: /* also known as a simple table */ query_specification /* SELECT statement */ | table_value_constructor /* VALUES statement */ | explicit_table /* TABLE statement */
You should be aware that
INTERSECT is evaluated
means that, for example,
TABLE x UNION TABLE y INTERSECT
TABLE z is always evaluated as
TABLE x UNION
(TABLE y INTERSECT TABLE z). See
Section 220.127.116.11, “INTERSECT Clause”, for more information.
In addition, you should keep in mind that, while the
operators are commutative (ordering is not significant),
EXCEPT is not (order of operands affects the
outcome). In other words, all of the following statements are
TABLE x UNION TABLE yand
TABLE y UNION TABLE xproduce the same result, although the ordering of the rows may differ. You can force them to be the same using
ORDER BY; see ORDER BY and LIMIT in Unions.
TABLE x INTERSECT TABLE yand
TABLE y INTERSECT TABLE xreturn the same result.
TABLE x EXCEPT TABLE yand
TABLE y EXCEPT TABLE xdo not yield the same result.
More information and examples can be found in the sections that follow.