UNION EXCEPT INTERSECT keywords

Overview#

UNION, 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#

UNION#

Flow chart showing the syntax of the UNION, EXCEPT & INTERSECT keyword

  • UNION returns distinct results.
  • UNION ALL returns all UNION results including duplicates.
  • EXCEPT returns distinct rows from the left input query that are not returned by the right input query.
  • EXCEPT ALL returns all EXCEPT results including duplicates.
  • INTERSECT returns distinct rows that are returned by both input queries.
  • INTERSECT ALL returns all INTERSECT results including duplicates.

Examples#

The examples for the set operations use the following tables:

sensor_1:

IDmakecity
1HoneywellNew York
2United AutomationMiami
3OmronMiami
4HoneywellSan Francisco
5OmronBoston
6RS ProBoston
1HoneywellNew York

Notice that the last row in the sensor_1 table is a duplicate.

sensor_2:

IDmakecity
1HoneywellSan Francisco
2United AutomationBoston
3EberleNew York
4HoneywellBoston
5OmronBoston
6RS ProBoston

UNION#

sensor_1 UNION sensor_2;

returns

IDmakecity
1HoneywellNew York
2United AutomationMiami
3OmronMiami
4HoneywellSan Francisco
5OmronBoston
6RS ProBoston
1HoneywellSan Francisco
2United AutomationBoston
3EberleNew York
4HoneywellBoston

UNION eliminates duplication even when one of the queries returns nothing.

For instance:

sensor_1
UNION
sensor_2 WHERE ID > 10;

returns:

IDmakecity
1HoneywellNew York
2United AutomationMiami
3OmronMiami
4HoneywellSan Francisco
5OmronBoston
6RS ProBoston

The duplicate row in sensor_1 is not returned as a result.

sensor_1 UNION ALL sensor_2;

returns

IDmakecity
1HoneywellNew York
2United AutomationMiami
3OmronMiami
4HoneywellSan Francisco
5OmronBoston
6RS ProBoston
1HoneywellSan Francisco
2United AutomationBoston
3EberleNew York
4HoneywellBoston
5OmronBoston
6RS ProBoston

EXCEPT#

sensor_1 EXCEPT sensor_2;

returns

IDmakecity
1HoneywellNew York
2United AutomationMiami
3OmronMiami
4HoneywellSan Francisco

Notice that EXCEPT eliminates duplicates. Let's run EXCEPT ALL to change that.

sensor_1 EXCEPT ALL sensor_2;
IDmakecity
1HoneywellNew York
2United AutomationMiami
3OmronMiami
4HoneywellSan Francisco
1HoneywellNew York

INTERSECT#

sensor_1 INTERSECT sensor_2;

returns

IDmakecity
5OmronBoston
6RS ProBoston

In this example we have no duplicates, but if there were any, we could use INTERSECT ALL to have them.

Keyword execution priority#

The QuestDB's engine processes the keywords from left to right, unless the priority is defined by parenthesis.

For example:

query_1 UNION query_2 EXCEPT query_3;

is executed as:

(query_1 UNION query_2) EXCEPT query_3;

Similarly, the following syntax:

query_1 UNION query_2 INTERSECT query_3;

is executed as:

(query_1 UNION query_2) INTERSECT query_3;

Clauses#

The 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:

query_1 UNION query_2
LIMIT 3;

is executed as:

(query_1 UNION query_2)
LIMIT 3;

For WHERE, the clause is applied first to the query immediate prior to it.

query_1 UNION query_2
WHERE value = 1;

is executed as:

query_1 UNION (query_2 WHERE value = 1);
note
  • QuestDB applies GROUP BY implicitly. See GROUP BY reference for more information.
  • Quest does not support the clause HAVING yet.

Alias#

When different aliases are used with set operations, the execution follows a left-right order and the output uses the first alias.

For example:

SELECT alias_1 FROM table_1
UNION
SELECT alias_2 FROM table_2;

The output shows alias_1.


โญ Something missing? Page not helpful? Please suggest an edit on GitHub.