Tim Tim - 21 days ago 8
SQL Question

what is the difference between an anti-join and an anti semi join?

From http://www.dbspecialists.com/files/presentations/semijoins.html


An “anti-join” between two tables returns rows from the first table
where no matches are found in the second table. An anti-join is
essentially the opposite of a semi-join: While a semi-join returns one
copy of each row in the first table for which at least one match is
found, an anti-join returns one copy of each row in the first table
for which no match is found
.


Isn't an anti semi-join instead of an anti-join the one which "returns one copy of each row in the first table for which no match is found"?

If it is, what is the definition of an anti-join?

Note that my question is at the general level of SQL, such as at the level of college text books on database concepts, not specific to a particular SQL RDBMS implementation

Thanks.

Answer

Anti-semijoin is anti join.

The antijoin can also be defined as the complement of the semijoin, as follows:

R ▷ S = R − R ⋉ S

Given this, the antijoin is sometimes called the anti-semijoin, and the antijoin operator is sometimes written as semijoin symbol with a bar above it, instead of ▷.

See https://en.wikipedia.org/wiki/Relational_algebras