AnalyticDB:UNION, INTERSECT, and EXCEPT

Syntax

query
{ UNION [ ALL ] | INTERSECT | EXCEPT }
query     

Parameters

• UNION: returns the union of two result sets.
• UNION ALL: returns the union of two result sets with duplicate rows retained. The ALL keyword indicates that the duplicate rows are retained after the UNION calculation.
• INTERSECT: returns the intersection of two result sets.
• EXCEPT: returns rows that appear in the first result set, but not in the other result set.

Calculation sequence

• The UNION and EXCEPT operators are left-associative. That is, if you do not use parentheses () to change the calculation sequence, the calculation starts from left to right.

  For example, in the following statement, the UNION operator returns the union of T1 and T2. Then, the EXCEPT operator returns the rows that appear only in the union returned by the UNION operator, but not T3.

  select * from t1
  union
  select * from t2
  except
  select * from t3
  order by c1;                 

• In the same statement, the INTERSECT operator is prioritized over the UNION and EXCEPT operators.

  For example, the following statement finds the intersection of T2 and T3, and then the union of the intersection and T1.

  select * from t1
  union
  select * from t2
  intersect
  select * from t3
  order by c1;                  

• You can use parentheses () to change the calculation sequence of these operators.

  For example, the following statement finds the union of T1 and T2, and then the intersection of the union and T3.

  (select * from t1
  union
  select * from t2)
  intersect
  (select * from t3)
  order by c1;