Skip to content
Developer Edition runs on one host and does not require a product license file. Need multi-host deployment or performance testing at scale? Explore Enterprise Edition

Composite query statement

The composite query statement combines the result sets of multiple linear query statements.

Limitations

The linear query statements to be combined must meet the following limitations:

  • The result sets that are passed to linear query statements must be of the same data types.

  • The result sets (for example, A and B) of the linear query statements must be column name-equal and column combinable, that is, the following conditions must be met:

    • The result sets A and B have the same number of columns.
    • For each column from the result set A, there is a column of the same name from the result set B, and vice versa.
    • The ordering of the columns from result sets A and B is the same.
    • For every pair of columns from result sets A and B, the data types of these columns are comparable. For more information, see Comparison predicate.

Syntax

composite_query_statement
linear_query_statement query_conjunction linear_query_statement query_conjunction
query_conjunction
UNION DISTINCT ALL EXCEPT DISTINCT ALL INTERSECT DISTINCT ALL 

composite_query_statement ::=
  linear_query_statement query_conjunction linear_query_statement ( query_conjunction linear_query_statement )*

query_conjunction ::=
  'UNION' ('DISTINCT' | 'ALL')? 
  | 'EXCEPT' ('DISTINCT' | 'ALL')? 
  | 'INTERSECT' ('DISTINCT' | 'ALL')?

Parameters

UNION | EXCEPT | INTERSECT

Specifies the set operator to be used when combining the result sets of two queries.

  • UNION: This operator combines all rows in the result sets of two queries into a single result set.
  • EXCEPT: This operator returns only the rows from the first result set that are not in the second result set.
  • INTERSECT: This operator returns only the rows that are in both result sets.

DISTINCT | ALL

Specifies whether to remove duplicate rows from the result set.

  • DISTINCT: Default value, removes duplicate rows from the result set.
  • ALL: Retains all rows in the result set, including duplicates.

linear_query_statement

Specifies a linear composition of simple query statements. For more information, see Linear query statement.

Examples

Combine two queries using the UNION DISTINCT operator

'''
FOR i IN LIST[4,1,2,3,3] RETURN i  
UNION DISTINCT 
FOR i IN LIST[11,2,2,22,33,33] RETURN i ORDER BY i OFFSET 1 LIMIT 2
'''

Return:

+----+
| i  |
+----+
| 4  |
| 1  |
| 2  |
| 3  |
| 11 |
+----+

Combine two queries using the UNION ALL operator

'''
FOR i IN LIST[4,1,2,3,3] RETURN i 
UNION ALL 
FOR i IN LIST[11,2,2,22,33,33] RETURN i ORDER BY i OFFSET 1 LIMIT 2
'''

Return:

+----+
| i  |
+----+
| 4  |
| 1  |
| 2  |
| 3  |
| 3  |
| 2  |
| 11 |
+----+

Combine two queries using the EXCEPT DISTINCT operator

'''
FOR i IN LIST[1,2,3,4] RETURN i AS num
EXCEPT DISTINCT 
FOR i IN LIST[2,4] RETURN i AS num
ORDER BY num
'''

Return:

+-----+
| num |
+-----+
| 1   |
| 3   |
+-----+

Combine two queries using the EXCEPT ALL operator

'''
FOR i IN LIST[4,1,2,3,2,3] RETURN i 
EXCEPT ALL 
FOR i IN LIST[11,2,2,22,33,33] RETURN i ORDER BY i OFFSET 1 LIMIT 2
'''

Return:

+---+
| i |
+---+
| 4 |
| 1 |
| 3 |
| 3 |
+---+

Combine two queries using the INTERSECT DISTINCT operator

'''
FOR i IN LIST[1,2,3,4] RETURN i AS num
INTERSECT DISTINCT 
FOR i IN LIST[3,4,5] RETURN i AS num
ORDER BY num
'''

Return:

+-----+
| num |
+-----+
| 3   |
| 4   |
+-----+

Combine two queries using the INTERSECT ALL operator

'''
FOR i IN LIST[4,1,2,3,2,3] RETURN i 
INTERSECT ALL 
FOR i IN LIST[11,2,2,22,33,33] RETURN i ORDER BY i OFFSET 1 LIMIT 2
'''

Return:

+---+
| i |
+---+
| 2 |
| 2 |
+---+