UNION, INTERSECT, and EXCEPT
- reference
Return Values
UNION
, INTERSECT
, and EXCEPT
return distinct results, such that there are no duplicates.
UNION ALL
, INTERSECT ALL
, and EXCEPT ALL
return all applicable values, including duplicates.
These queries are faster, because they do not compute distinct results.
You can improve the performance of a query by using covering indexes, where the index includes all the information needed to satisfy the query. For more information, see Covering Indexes.
To order all the results of a set operator together, refer to the examples for the ORDER BY clause.
Examples
For the following examples, consider these queries and results.
n1qlSELECT DISTINCT city FROM `travel-sample`.inventory.airport;
(1641 results)
n1qlSELECT DISTINCT city FROM `travel-sample`.inventory.hotel;
(274 results)
The SELECT statements in the following examples do not need to use the DISTINCT keyword, since the set operators return distinct results when used without the ALL keyword.
|