Skip to content

Combination query (UNION, INTERSECT, MINUS)

The results of the two queries can be combined with the UNION, INTERSECT and MINUS syntax.

The example syntax is as follows:

query1 UNION [ALL] query2
query1 INTERSECT [ALL] query2
query1 MINUS [ALL] query2

Tips: query1 and query2 are queries that can use any of the features discussed so far.

UNION effectively merges the results of query2 into the results of query1 (but there is no guarantee that this is the order of return rows). Furthermore, it is the same syntax as DISTINCT, which eliminates duplicate rows from the result; UNION ALL is used, which means that no duplicate rows from the result.

INTERSECT returns all rows in the result where query1 and query2 intersect. If INTERSECT ALL is not used, duplicate rows in the result are eliminated; if INTERSECT ALL is used, duplicate rows in the result are not eliminated.

MINUS returns query1 result, but not all rows in query2. That is, the difference between the results of query1 and query2. Similarly, if MINUS ALL is not used, duplicate rows in the result are eliminated; if MINUS ALL is not used, duplicate rows in the result are not eliminated.

To calculate union, intersection, or difference of two queries, the two queries must be "union-compatible", meaning they return the same number of columns and the corresponding columns have compatible data types.

UNION, INTERSECT and MINUS operations can be combined, for example:

query1 UNION query2 MINUS query3

It is also equivalent to:

(query1 UNION query2) MINUS query3

As shown in the above line of code, you can use brackets to control the order of calculations. If there are no brackets, UNION and MINUS are associated from left to right. But INTERSECT has higher precedence than these two operators, so see the following line of code:

query1 UNION query2 INTERSECT query3

express:

query1 UNION (query2 INTERSECT query3)

You can also enclose individual queries in brackets. If the query requires the use of the clause in the following example (such as the LIMIT clause), if there are no brackets, it will result in a syntax error, which will be understood during the calculation as the output applied to the combination operation rather than one of its inputs. As shown in the following example:

SELECT a FROM b UNION SELECT x FROM y LIMIT 10

It is acceptable, but it represents the calculation order as follows:

(SELECT a FROM b UNION SELECT x FROM y) LIMIT 10

Instead of the following calculation order:

SELECT a FROM b UNION (SELECT x FROM y LIMIT 10)

refer to

For documentation on the single syntax of UNION, INTERSECT and MINUS, see the following: