Set Operations

# Set Operations #

Set Operations are used to combine multiple `SELECT` statements into a single result set. Hive dialect supports the following operations:

• UNION
• INTERSECT
• EXCEPT/MINUS

## UNION #

### Description #

`UNION`/`UNION DISTINCT`/`UNION ALL` returns the rows that are found in either side.

`UNION` and `UNION DISTINCT` only returns the distinct rows, while `UNION ALL` does not duplicate.

### Syntax #

``````<query> { UNION [ ALL | DISTINCT ] } <query> [ .. ]
``````

### Examples #

``````SELECT x, y FROM t1 UNION DISTINCT SELECT x, y FROM t2;
SELECT x, y FROM t1 UNION SELECT x, y FROM t2;
SELECT x, y FROM t1 UNION ALL SELECT x, y FROM t2;
``````

## INTERSECT #

### Description #

`INTERSECT`/`INTERSECT DISTINCT`/`INTERSECT ALL` returns the rows that are found in both side.

`INTERSECT`/`INTERSECT DISTINCT` only returns the distinct rows, while `INTERSECT ALL` does not duplicate.

### Syntax #

``````<query> { INTERSECT [ ALL | DISTINCT ] } <query> [ .. ]
``````

### Examples #

``````SELECT x, y FROM t1 INTERSECT DISTINCT SELECT x, y FROM t2;
SELECT x, y FROM t1 INTERSECT SELECT x, y FROM t2;
SELECT x, y FROM t1 INTERSECT ALL SELECT x, y FROM t2;
``````

## EXCEPT/MINUS #

### Description #

`EXCEPT`/`EXCEPT DISTINCT`/`EXCEPT ALL` returns the rows that are found in left side but not in right side.

`EXCEPT`/`EXCEPT DISTINCT` only returns the distinct rows, while `EXCEPT ALL` does not duplicate.

`MINUS` is synonym for `EXCEPT`.

### Syntax #

``````<query> { EXCEPT [ ALL | DISTINCT ] } <query> [ .. ]
``````

### Examples #

``````SELECT x, y FROM t1 EXCEPT DISTINCT SELECT x, y FROM t2;
SELECT x, y FROM t1 EXCEPT SELECT x, y FROM t2;
SELECT x, y FROM t1 EXCEPT ALL SELECT x, y FROM t2;
``````