Hi, I am Emmanuel Katto from UK. I want to ask in which scenarios we need to use semi join?
Thanks!
Emmanuel
The definition of the semi join is indicative of where you might need to use it. As an example, imagine you have 3 tables in your database. 1. Classes offered at a university (with Class Id as the primary key), 2. Students at the university (with student Id as the primary key) and 3. A table with two columns - class Id and student Id. This third table will have entry if a student has signed up for a class.
Now if you are given a set of 5 student id's and asked to find which classes at least one of them have signed up for, how would you write a query? What you do is conceptually a semi-join.
As background, a "standard" inner join is where both sides of the join must match to produce a result. For example:
FROM A INNER JOIN B ON A.col = B.col
If table A has col = 1 ,but table B does not, then no result is returned, because both sides did not match.
By contrast, a semijoin is one in which one side of the joined result may be missing a match.
For example:
FROM A LEFT OUTER JOIN B ON A.col = B.col
If table A has col = 1, the columns from A will still be listed, but all columns from table B will show as NULL (since they didn't exist).
A semijoin can be either a LEFT join or a RIGHT join.