When we want to combine data with the same schema together, we often use set operations. Regular set operations in the relational database are INTERSECT, MINUS, and UNION/UNION ALL. HQL only supports UNION and UNION ALL. The difference between them is that UNION ALL does not remove duplicate rows while UNION does. In addition, all unioned data must have the same name and data type, or else an implicit conversion will be done and may cause a runtime exception. If ORDER BY, SORT BY, CLUSTER BY, DISTRIBUTE BY, or LIMIT are used, they are applied to the whole result set after the union:

> SELECT a.name as nm FROM employee a
> UNION ALL -- Use column alias to make the same name for union
> SELECT b.name as nm FROM employee_hr b;
+-----------+
|    nm     |
+-----------+
| Michael   |
| Will      |
| Shelley   |
| Lucy      |
| Michael   |
| Will      |
| Steven    |
| Lucy      |
+-----------+
8 rows selected (23.919 seconds)
 
> SELECT a.name as nm FROM employee a
> UNION -- UNION removes duplicated names and slower
> SELECT b.name as nm FROM employee_hr b;
+----------+
|    nm    |
+----------+
| Lucy     |
| Michael  |
| Shelley  |
| Steven   |
| Will     |
+----------+
5 rows selected (32.221 seconds)

-- Order by applies to the unioned data
-- When you want to order only one data set,
-- Use order in the subquery
> SELECT a.name as nm FROM employee a
> UNION ALL 
> SELECT b.name as nm FROM employee_hr b
> ORDER BY nm;
+----------+
|    nm    |
+----------+
| Lucy     |
| Lucy     |
| Michael  |
| Michael  |
| Shelley  |
| Steven   |
| Will     |
| Will     |
+----------+

For other set operations that HQL does not support yet, such as INTERCEPT and MINUS, we can use joins or left join to implement them as follows:

-- Use join for set intercept
> SELECT a.name
> FROM employee a
> JOIN employee_hr b ON a.name = b.name;
+----------+
|  a.name  |
+----------+
| Michael  |
| Will     |
| Lucy     |
+----------+
3 rows selected (44.862 seconds)

-- Use left join for set minus
> SELECT a.name
> FROM employee a
> LEFT JOIN employee_hr b ON a.name = b.name
> WHERE b.name IS NULL;
+----------+
|  a.name  |
+----------+
| Shelley  |
+----------+
1 row selected (36.841 seconds)