What is difference between natural join and semi join?

Answer 1

The result of the natural join is the set of all combinations of tuples in R and S that are equal on their common attribute names.

The result of the semijoin is only the set of all tuples in R for which there is a tuple in S that is equal on their common attribute names.

The point is that natural join is a set of all combinations and semijoin is only the tuples from the first relation not a combination between the two.

For example:

R1 R2

A B B C

1 2 2 3

1 3 3 4

1 4 5 3

R1 (natural join) R2 =

A B C

1 2 3

1 3 4

whereas R1(semijoin) R2 =

A B

1 2

1 3

So in a way semijoin selects and returns a table of only the tuples from R1 that have an equal attribute with R2

Answer 2

A natural join combines two tables based on columns with the same name, eliminating duplicate columns. A semi join returns rows from the first table if they have a matching row in the second table, but does not include the actual values from the second table.