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;