Difference between INNER JOIN and LEFT SEMI JOIN

Question

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

User · Answer

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 ¦
+--------------+

User · Answer

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

User · Answer

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

User · Answer

Trying to depict with venn diagrams for better understanding   Left Semi join   A semi join returns values from the left side of the relation that has a match with the right  It is also referred to as a left semi join   Note   There is another thing called left anti join   An anti join returns values from the left relation that has no match with the right  It is also referred to as a left anti join  Inner join    It selects rows that have matching values in both relations

[sql] Difference between INNER JOIN and LEFT SEMI JOIN

Examples related to sql

Examples related to hql

Examples related to hive