Set operation – UNION ALL

To operate the result set vertically, Hive only supports UNION ALL right now. And, the result set of UNION ALL keeps duplicates if any. Before Hive 0.13.0, UNION ALL can only be used in the subquery. Since Hive 0.13.0, UNION ALL can also be used in top-level queries. The following are examples of the UNION ALL statements:

• Check the name column in the employee_hr and employee table:

  jdbc:hive2://> SELECT name FROM employee_hr;
  +----------+
  |   name   |
  +----------+
  | Michael  |
  | Will     |
  | Steven   |
  | Lucy     |
  +----------+
  4 rows selected (0.116 seconds)
  
  jdbc:hive2://> SELECT name FROM employee;
  +----------+
  |   name   |
  +----------+
  | Michael  |
  | Will     |
  | Shelley  |
  | Lucy     |
  +----------+
  4 rows selected (0.049 seconds)
  

• Use UNION on the name column from both tables, including duplications:

  jdbc:hive2://> SELECT a.name 
  . . . . . . .> FROM employee a
  . . . . . . .> UNION ALL
  . . . . . . .> SELECT b.name 
  . . . . . . .> FROM employee_hr...