Impala SQL Join is a clause that is used for combining specific fields from two or more tables based on the common columns. The joins in the Impala are similar to the SQL and Hive joins. Joins are used to combine rows from multiple tables. In this article, we will learn about different Impala SQL join types with examples.
Different Impala Join Types
Following are Different Hive Join Types
- INNER JOIN
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
- SEMI JOIN
- ANTI JOIN
- CROSS JOIN
Below are the tables that we will be using to demonstrate different Join types in Cloudera Impala:
Query: select * from students +----+------+-----------+ | id | name | city | +----+------+-----------+ | 2 | BCD | Mumbai | | 5 | EFG | Bangalore | | 1 | ABC | London | | 4 | DEF | Mumbai | | 3 | CDE | Bangalore | +----+------+-----------+ Fetched 5 row(s) in 1.52s Dept: Query: select * from dept +------+---------+-----+ | code | subject | sid | +------+---------+-----+ | 102 | Physics | 2 | | 103 | Chem | 3 | | 101 | Maths | 1 | | 100 | CS | 1 | +------+---------+-----+ Fetched 4 row(s) in 0.43s
Impala INNER JOIN or Simply JOIN
Impala Inner join or Join returns matching columns from two or more tables.
Syntax and Examples
SELECT ID, code FROM students INNER JOIN dept ON (ID = sid); Result: +----+------+ | id | code | +----+------+ | 2 | 102 | | 3 | 103 | | 1 | 101 | | 1 | 100 | +----+------+
Impala LEFT OUTER JOIN or LEFT JOIN
You can use the name LEFT OUTER JOIN or LEFT JOIN to combine two or more tables. This type of join returns all rows from the left table even if there is no matching row available in the right table. Unmatched right table’s records will be NULL.
Syntax and Examples:
SELECT ID, code FROM students LEFT OUTER JOIN dept ON (ID = sid); Results: +----+------+ | id | code | +----+------+ | 5 | NULL | | 4 | NULL | | 3 | 103 | | 2 | 102 | | 1 | 101 | | 1 | 100 | +----+------+
Impala RIGHT OUTER JOIN or RIGHT JOIN
You can use the name RIGHT OUTER JOIN or RIGHT JOIN to combine two or more tables. This type of join returns all rows from the right table even if there is no matching row available in the left table. Unmatched left table’s records will be NULL.
Syntax and Examples
SELECT ID, code FROM students RIGHT OUTER JOIN dept ON (ID = sid); Result: +----+------+ | id | code | +----+------+ | 1 | 101 | | 1 | 100 | | 3 | 103 | | 2 | 102 | +----+------+
Impala FULL OUTER JOIN
It returns all rows from the both tables that fulfill the JOIN condition. The unmatched rows from both tables will be returned as a NULL.
Syntax and Examples
SELECT ID, code FROM students FULL OUTER JOIN dept ON (ID = sid); Results: +----+------+ | id | code | +----+------+ | 4 | NULL | | 5 | NULL | | 1 | 101 | | 1 | 100 | | 2 | 102 | | 3 | 103 | +----+------+
Impala SEMI JOIN
These types of joins are rarely used in Impala SQL. You can use LEFT and RIGHT along with SEMI JOIN. With the left semi join, only data from the left table is returned for rows where there is matching data in the right table. Only one instance of each row from the left table is returned, regardless of how many matching rows exist in the right table.
The right semi join is reverse of the left semi join.
Syntax and Examples
SELECT ID FROM students LEFT SEMI JOIN dept ON (ID = sid); Results: +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ SELECT code FROM students RIGHT SEMI JOIN dept ON (ID = sid); Results: +------+ | code | +------+ | 102 | | 101 | | 100 | | 103 | +------+
Impala ANIT JOIN
The LEFT and RIGHT keywords are required for ANTI JOIN. For LEFT ANTI JOIN, this clause returns those values from the left-hand table that have no matching value in the right-hand table. And vice-versa for RIGHT ANTI JOIN.
Syntax and Examples
SELECT ID FROM students LEFT ANTI JOIN dept ON (ID = sid); Results: +----+ | id | +----+ | 5 | | 4 | +----+ SELECT CODE FROM students RIGHT ANTI JOIN dept ON (ID = sid);
Impala CROSS JOIN or Cartesian Join
To avoid huge data set producing, Impala does not allow the CROSS JOIN without where condition. The CROSS JOIN produces a result set with all combinations of rows from the left and right tables.
Syntax and Examples
SELECT ID,CODE FROM students CROSS JOIN dept where ID = 4 Result: +----+------+ | id | code | +----+------+ | 4 | 100 | | 4 | 103 | | 4 | 102 | | 4 | 101 | +----+------+