You can use the UNION, INTERSECT, and EXCEPT operators to combine multiple query result sets to a single result set.

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;