semi join

the semantics of semi-join are as follows: a row of T1 is returned as soon as T1.X finds a match with any value of T2.Y without searching for further matches. this is in contrast to finding all possible matches in inner join.
consider the following schema:

EMPLOYEE (Ssn, Bdate, Address, Sex, Salary, Dno)
DEPARTMENT (Dnumber, Dname, Dmgrssn, Zipcode)

where a department is located in a specific zip code.
let us consider the following query:

Q(SJ): SELECT COUNT(*)
FROM  DEPARTMENT D
WHERE D.Dnumber IN (SELECT E.Dno
                    FROM   EMPLOYEE E
                    WHERE  E.Salary > 200000)

here we have a nested query which is joined by the connector IN.
to remove the nested query:

(SELECT E.Dno
 FROM   EMPLOYEE E
 WHERE  E.Salary > 200000)

is called as unnesting. it leads to the following query with an operation called semi-join, which we show with a non-standard notation "S=" below:

SELECT COUNT(*)
FROM EMPLOYEE E, DEPARTMENT D
WHERE D.Dnumber S= E.Dno and E.Salary > 200000;

the above query is counting the number of departments that have employees who make more than $200,000 annually. here, the operation is to find the department whose Dnumber attribute matches the value(s) for the Dno attribute of Employee with that high salary.
in algebra, alternate notations exist. one common notation is $\text{[math]}$ .
[cite:@elmasri_db_2015 chapter 18.1.1 additional operators semi-join and anti-join]