Movatterモバイル変換

The results of two queries can be combined using the set operations union, intersection, and difference. The syntax is

query1 UNION [ALL]query2query1 INTERSECT [ALL]query2query1 EXCEPT [ALL]query2

wherequery1 andquery2 are queries that can use any of the features discussed up to this point.

UNION effectively appends the result ofquery2 to the result ofquery1 (although there is no guarantee that this is the order in which the rows are actually returned). Furthermore, it eliminates duplicate rows from its result, in the same way asDISTINCT, unlessUNION ALL is used.

INTERSECT returns all rows that are both in the result ofquery1 and in the result ofquery2. Duplicate rows are eliminated unlessINTERSECT ALL is used.

EXCEPT returns all rows that are in the result ofquery1 but not in the result ofquery2. (This is sometimes called thedifference between two queries.) Again, duplicates are eliminated unlessEXCEPT ALL is used.

In order to calculate the union, intersection, or difference of two queries, the two queries must be“union compatible”, which means that they return the same number of columns and the corresponding columns have compatible data types, as described inSection 10.5.

Set operations can be combined, for example

query1 UNIONquery2 EXCEPTquery3

which is equivalent to

(query1 UNIONquery2) EXCEPTquery3

As shown here, you can use parentheses to control the order of evaluation. Without parentheses,UNION andEXCEPT associate left-to-right, butINTERSECT binds more tightly than those two operators. Thus

query1 UNIONquery2 INTERSECTquery3

means

query1 UNION (query2 INTERSECTquery3)

You can also surround an individualquery with parentheses. This is important if thequery needs to use any of the clauses discussed in following sections, such asLIMIT. Without parentheses, you'll get a syntax error, or else the clause will be understood as applying to the output of the set operation rather than one of its inputs. For example,

SELECT a FROM b UNION SELECT x FROM y LIMIT 10

is accepted, but it means

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

not

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