Semi Join Query - Emmanuel Katto

1

Hi, I am Emmanuel Katto from UK. I want to ask in which scenarios we need to use semi join?

Thanks!
Emmanuel

2

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.

3

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.

4

Thanks for your answers. @JamesK and @ScottPletcher