These lack the syntactic sugar of the cartesian joins. I also find that the syntax of semi-joins tends to more natural human language.
Another big upside is that they are faster than cartesian joins because in SQL they avoid the need to compute the cartesian product, which is an expensive operation.
Just a quick note on the algebra for this section. Sometimes you will come across notation for semi join and anti join expressed in terms of the cartesian product. Ignore these. The way we perform these joins in SQL will not compute anything resembling a cartesian product at all!
In a semi join, we simply check the yes/no response to whether any given tuple in matches to a record in .
Unlike a cartesian join, we only return data from .
SELECT * FROM R WHERE EXISTS (SELECT 1 FROM S WHERE R.alpha = beta)
- You only want to return columns from .
- Data from must match to data in
Use case is same as the semi join except you wish to return the disjoint set.
SELECT * FROM R WHERE NOT EXISTS (SELECT 1 FROM S WHERE R.alpha = beta)
The examples on this page only look at these joins when used with
But of course, you can use them with other DML operations.
However, I would advise on being extra careful when running
DELETE against the ouput of a semi or anti join.