UNION EXCEPT INTERSECT keywords
#
OverviewUNION
, EXCEPT
, and INTERSECT
perform set operations.
UNION
is used to combine the results of two or more queries.
EXCEPT
and INTERSECT
return distinct rows by comparing the results of two
queries.
To work properly, all of the following must be true:
- Each query statement should return the same number of column.
- Each column to be combined should have data types that are either the same, or
supported by
implicit cast
. See CAST for more information. - Columns in each query statement should be in the same order.
#
Syntax#
UNIONUNION
returns distinct results.UNION ALL
returns allUNION
results including duplicates.EXCEPT
returns distinct rows from the left input query that are not returned by the right input query.EXCEPT ALL
returns allEXCEPT
results including duplicates.INTERSECT
returns distinct rows that are returned by both input queries.INTERSECT ALL
returns allINTERSECT
results including duplicates.
#
ExamplesThe examples for the set operations use the following tables:
sensor_1:
ID | make | city |
---|---|---|
1 | Honeywell | New York |
2 | United Automation | Miami |
3 | Omron | Miami |
4 | Honeywell | San Francisco |
5 | Omron | Boston |
6 | RS Pro | Boston |
1 | Honeywell | New York |
Notice that the last row in the sensor_1 table is a duplicate.
sensor_2:
ID | make | city |
---|---|---|
1 | Honeywell | San Francisco |
2 | United Automation | Boston |
3 | Eberle | New York |
4 | Honeywell | Boston |
5 | Omron | Boston |
6 | RS Pro | Boston |
#
UNIONreturns
ID | make | city |
---|---|---|
1 | Honeywell | New York |
2 | United Automation | Miami |
3 | Omron | Miami |
4 | Honeywell | San Francisco |
5 | Omron | Boston |
6 | RS Pro | Boston |
1 | Honeywell | San Francisco |
2 | United Automation | Boston |
3 | Eberle | New York |
4 | Honeywell | Boston |
UNION
eliminates duplication even when one of the queries returns nothing.
For instance:
returns:
ID | make | city |
---|---|---|
1 | Honeywell | New York |
2 | United Automation | Miami |
3 | Omron | Miami |
4 | Honeywell | San Francisco |
5 | Omron | Boston |
6 | RS Pro | Boston |
The duplicate row in sensor_1
is not returned as a result.
returns
ID | make | city |
---|---|---|
1 | Honeywell | New York |
2 | United Automation | Miami |
3 | Omron | Miami |
4 | Honeywell | San Francisco |
5 | Omron | Boston |
6 | RS Pro | Boston |
1 | Honeywell | San Francisco |
2 | United Automation | Boston |
3 | Eberle | New York |
4 | Honeywell | Boston |
5 | Omron | Boston |
6 | RS Pro | Boston |
#
EXCEPTreturns
ID | make | city |
---|---|---|
1 | Honeywell | New York |
2 | United Automation | Miami |
3 | Omron | Miami |
4 | Honeywell | San Francisco |
Notice that EXCEPT
eliminates duplicates. Let's run EXCEPT ALL
to change
that.
ID | make | city |
---|---|---|
1 | Honeywell | New York |
2 | United Automation | Miami |
3 | Omron | Miami |
4 | Honeywell | San Francisco |
1 | Honeywell | New York |
#
INTERSECTreturns
ID | make | city |
---|---|---|
5 | Omron | Boston |
6 | RS Pro | Boston |
In this example we have no duplicates, but if there were any, we could use
INTERSECT ALL
to have them.
#
Keyword execution priorityThe QuestDB's engine processes the keywords from left to right, unless the priority is defined by parenthesis.
For example:
is executed as:
Similarly, the following syntax:
is executed as:
#
ClausesThe set operations can be used with clauses such as LIMIT
, ORDER BY
, and
WHERE
. However, when the clause keywords are added after the set operations,
the execution order for different clauses varies.
For LIMIT
and ORDER BY
, the clauses are applied after the set operations.
For example:
is executed as:
For WHERE
, the clause is applied first to the query immediate prior to it.
is executed as:
note
- QuestDB applies
GROUP BY
implicitly. See GROUP BY reference for more information. - Quest does not support the clause
HAVING
yet.
#
AliasWhen different aliases are used with set operations, the execution follows a left-right order and the output uses the first alias.
For example:
The output shows alias_1
.