Hash Semi-join
Hash semi-join is used to return the records found in the other table. Unlike plain join, it returns each matching record from the leading table only once, not regarding how many matches are there in the IN table.
As with the anti-join, semi-join can also be left and right:
Read more about this topic: Hash Join