Difference between INNER JOIN and LEFT SEMI JOIN
SqlHqlHiveSql Problem Overview
What is the difference between an INNER JOIN and LEFT SEMI JOIN?
In the scenario below, why am I getting two different results?
The INNER JOIN result set is a lot larger. Can someone explain? I am trying to get the names within table_1 that only appear in table_2.
SELECT name
FROM table_1 a
INNER JOIN table_2 b ON a.name=b.name
SELECT name
FROM table_1 a
LEFT SEMI JOIN table_2 b ON (a.name=b.name)
Sql Solutions
Solution 1 - Sql
An INNER JOIN can return data from the columns from both tables, and can duplicate values of records on either side have more than one match. A LEFT SEMI JOIN can only return columns from the left-hand table, and yields one of each record from the left-hand table where there is one or more matches in the right-hand table (regardless of the number of matches). It's equivalent to (in standard SQL):
SELECT name
FROM table_1 a
WHERE EXISTS(
SELECT * FROM table_2 b WHERE (a.name=b.name))
If there are multiple matching rows in the right-hand column, an INNER JOIN will return one row for each match on the right table, while a LEFT SEMI JOIN only returns the rows from the left table, regardless of the number of matching rows on the right side. That's why you're seeing a different number of rows in your result.
> I am trying to get the names within table_1 that only appear in table_2.
Then a LEFT SEMI JOIN is the appropriate query to use.
Solution 2 - Sql
Suppose there are 2 tables TableA and TableB with only 2 columns (Id, Data) and following data:
TableA:
+----+---------+
| Id | Data |
+----+---------+
| 1 | DataA11 |
| 1 | DataA12 |
| 1 | DataA13 |
| 2 | DataA21 |
| 3 | DataA31 |
+----+---------+
TableB:
+----+---------+
| Id | Data |
+----+---------+
| 1 | DataB11 |
| 2 | DataB21 |
| 2 | DataB22 |
| 2 | DataB23 |
| 4 | DataB41 |
+----+---------+
Inner Join on column Id will return columns from both the tables and only the matching records:
.----.---------.----.---------.
| Id | Data | Id | Data |
:----+---------+----+---------:
| 1 | DataA11 | 1 | DataB11 |
:----+---------+----+---------:
| 1 | DataA12 | 1 | DataB11 |
:----+---------+----+---------:
| 1 | DataA13 | 1 | DataB11 |
:----+---------+----+---------:
| 2 | DataA21 | 2 | DataB21 |
:----+---------+----+---------:
| 2 | DataA21 | 2 | DataB22 |
:----+---------+----+---------:
| 2 | DataA21 | 2 | DataB23 |
'----'---------'----'---------'
Left Join (or Left Outer join) on column Id will return columns from both the tables and matching records with records from left table (Null values from right table):
.----.---------.----.---------.
| Id | Data | Id | Data |
:----+---------+----+---------:
| 1 | DataA11 | 1 | DataB11 |
:----+---------+----+---------:
| 1 | DataA12 | 1 | DataB11 |
:----+---------+----+---------:
| 1 | DataA13 | 1 | DataB11 |
:----+---------+----+---------:
| 2 | DataA21 | 2 | DataB21 |
:----+---------+----+---------:
| 2 | DataA21 | 2 | DataB22 |
:----+---------+----+---------:
| 2 | DataA21 | 2 | DataB23 |
:----+---------+----+---------:
| 3 | DataA31 | | |
'----'---------'----'---------'
Right Join (or Right Outer join) on column Id will return columns from both the tables and matching records with records from right table (Null values from left table):
┌────┬─────────┬────┬─────────┐
│ Id │ Data │ Id │ Data │
├────┼─────────┼────┼─────────┤
│ 1 │ DataA11 │ 1 │ DataB11 │
│ 1 │ DataA12 │ 1 │ DataB11 │
│ 1 │ DataA13 │ 1 │ DataB11 │
│ 2 │ DataA21 │ 2 │ DataB21 │
│ 2 │ DataA21 │ 2 │ DataB22 │
│ 2 │ DataA21 │ 2 │ DataB23 │
│ │ │ 4 │ DataB41 │
└────┴─────────┴────┴─────────┘
Full Outer Join on column Id will return columns from both the tables and matching records with records from left table (Null values from right table) and records from right table (Null values from left table):
╔════╦═════════╦════╦═════════╗
║ Id ║ Data ║ Id ║ Data ║
╠════╬═════════╬════╬═════════╣
║ - ║ ║ ║ ║
║ 1 ║ DataA11 ║ 1 ║ DataB11 ║
║ 1 ║ DataA12 ║ 1 ║ DataB11 ║
║ 1 ║ DataA13 ║ 1 ║ DataB11 ║
║ 2 ║ DataA21 ║ 2 ║ DataB21 ║
║ 2 ║ DataA21 ║ 2 ║ DataB22 ║
║ 2 ║ DataA21 ║ 2 ║ DataB23 ║
║ 3 ║ DataA31 ║ ║ ║
║ ║ ║ 4 ║ DataB41 ║
╚════╩═════════╩════╩═════════╝
Left Semi Join on column Id will return columns only from left table and matching records only from left table:
┌────┬─────────┐
│ Id │ Data │
├────┼─────────┤
│ 1 │ DataA11 │
│ 1 │ DataA12 │
│ 1 │ DataA13 │
│ 2 │ DataA21 │
└────┴─────────┘
Solution 3 - Sql
Tried in Hive and got the below output
table1 >1,wqe,chennai,india > >2,stu,salem,india > >3,mia,bangalore,india > >4,yepie,newyork,USA
table2 >1,wqe,chennai,india > >2,stu,salem,india > >3,mia,bangalore,india > >5,chapie,Los angels,USA
Inner Join >SELECT * FROM table1 INNER JOIN table2 ON (table1.id = table2.id); > >1 wqe chennai india 1 wqe chennai india > >2 stu salem india 2 stu salem india > >3 mia bangalore india 3 mia bangalore india
Left Join >SELECT * FROM table1 LEFT JOIN table2 ON (table1.id = table2.id); > >1 wqe chennai india 1 wqe chennai india > >2 stu salem india 2 stu salem india > >3 mia bangalore india 3 mia bangalore india > >4 yepie newyork USA NULL NULL NULL NULL
Left Semi Join >SELECT * FROM table1 LEFT SEMI JOIN table2 ON (table1.id = table2.id); > > 1 wqe chennai india > > 2 stu salem india > > 3 mia bangalore india > > note: Only records in left table are displayed whereas for Left Join both the table records displayed
Solution 4 - Sql
All above answers are correct. However in practice, it helps to associate the mental model of a filter when imagining LEFT SEMI JOIN.
The answer is a subset of rows from LEFT table, which have a match in RIGHT TABLE.