Besides INNER JOIN, HQL also supports regular OUTER JOIN and FULL JOIN. The logic of such a join is the same as what's in the SQL. The following table summarizes the differences between common joins. Here, we assume table_m has m rows and table_n has n rows with one-to-one mapping:
| Join type | Logic | Rows returned |
|
table_m JOIN table_n |
This returns all rows matched in both tables. | m ∩ n |
|
table_m LEFT JOIN table_n |
This returns all rows in the left table and matched rows in the right table. If there is no match in the right table, it returns NULL in the right table. | m |
|
table_m RIGHT JOIN table_n |
This returns all rows in the right table and matched rows in the left table. If there is no match in the left table, it returns NULL in the left table. | n |
|
table_m FULL JOIN table_n |
This returns all rows in both tables and matched rows in both tables. If there is no match in the left or right table, it returns NULL instead. | m + n - m ∩ n |
|
table_m CROSS JOIN table_n |
This returns all row combinations in both the tables to produce a Cartesian product. | m * n |
The following examples demonstrate the different OUTER JOINs:
> SELECT
> emp.name, emph.sin_number > FROM employee emp -- All rows in left table returned > LEFT JOIN employee_hr emph ON emp.name = emph.name; +-----------+------------------+ | emp.name | emph.sin_number | +-----------+------------------+ | Michael | 547-968-091 | | Will | 527-948-090 | | Shelley | NULL | -- NULL for mismatch | Lucy | 577-928-094 | +-----------+------------------+ 4 rows selected (39.637 seconds)
> SELECT
> emp.name, emph.sin_number > FROM employee emp -- All rows in right table returned > RIGHT JOIN employee_hr emph ON emp.name = emph.name;
+-----------+------------------+ | emp.name | emph.sin_number | +-----------+------------------+ | Michael | 547-968-091 | | Will | 527-948-090 | | NULL | 647-968-598 | -- NULL for mismatch | Lucy | 577-928-094 |
+-----------+------------------+ 4 rows selected (34.485 seconds)
> SELECT
> emp.name, emph.sin_number > FROM employee emp -- Rows from both side returned > FULL JOIN employee_hr emph ON emp.name = emph.name; +-----------+------------------+ | emp.name | emph.sin_number | +-----------+------------------+ | Lucy | 577-928-094 | | Michael | 547-968-091 | | Shelley | NULL | -- NULL for mismatch | NULL | 647-968-598 | -- NULL for mismatch | Will | 527-948-090 | +-----------+------------------+ 5 rows selected (64.251 seconds)
The CROSS JOIN statement does not have a join condition. The CROSS JOIN statement can also be written using join without condition or with the always true condition, such as 1 = 1. In this case, we can join any datasets with cross joins. However, we only consider using such joins when we have to link data without relations in nature, such as adding headers with a row count to a table. The following are three equal ways of writing CROSS JOIN:
> SELECT
> emp.name, emph.sin_number > FROM employee emp > CROSS JOIN employee_hr emph;
> SELECT
> emp.name, emph.sin_number > FROM employee emp > JOIN employee_hr emph;
> SELECT
> emp.name, emph.sin_number > FROM employee emp > JOIN employee_hr emph on 1=1; +-----------+------------------+ | emp.name | emph.sin_number | +-----------+------------------+ | Michael | 547-968-091 | | Michael | 527-948-090 | | Michael | 647-968-598 | | Michael | 577-928-094 | | Will | 547-968-091 | | Will | 527-948-090 | | Will | 647-968-598 | | Will | 577-928-094 | | Shelley | 547-968-091 | | Shelley | 527-948-090 | | Shelley | 647-968-598 | | Shelley | 577-928-094 | | Lucy | 547-968-091 | | Lucy | 527-948-090 | | Lucy | 647-968-598 | | Lucy | 577-928-094 | +-----------+------------------+ 16 rows selected (34.924 seconds)
Although Hive did not support unequal joins explicitly in the earlier version, there are workarounds by using CROSS JOIN and WHERE, as in this example:
> SELECT
> emp.name, emph.sin_number > FROM employee emp > CROSS JOIN employee_hr emph
> WHERE emp.name <> emph.name; +-----------+------------------+ | emp.name | emph.sin_number | +-----------+------------------+ | Michael | 527-948-090 | | Michael | 647-968-598 | | Michael | 577-928-094 | | Will | 547-968-091 | | Will | 647-968-598 | | Will | 577-928-094 | | Shelley | 547-968-091 | | Shelley | 527-948-090 | | Shelley | 647-968-598 | | Shelley | 577-928-094 | | Lucy | 547-968-091 | | Lucy | 527-948-090 | | Lucy | 647-968-598 | +-----------+------------------+ 13 rows selected (35.016 seconds)