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)
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.