SEMIJOIN

SEMIJOIN is a way to filter a RowSet using another RowSet.

A simple example is: "Find all the employees in this RowSet A, where the employee appears also in RowSet B".

There are two variants:

• LEFT SEMIJOIN -> Give only those rows in the left rowset that have a matching row in the right rowset.
• RIGHT SEMIJOIN -> Give only those rows in the right rowset that have a matching row in the left rowset.

NOTE: If you leave out LEFT or RIGHT, and instead simply write SEMIJOIN then what you get is LEFT SEMIJOIN. Do not leave out LEFT or RIGHT always explicitly it.

Find all employees that are in valid departments

@left_semijoin1 =
    SELECT
        @employees.DepID,    
        @employees.EmpName
    FROM @employees
    LEFT SEMIJOIN @departments
        ON @employees.DepID == @departments.DepID;

Find all departments that has an employee listed in the employee RowSet.

@left_semijoin2 =
    SELECT 
        @departments.DepID,
        @departments.DepName
    FROM @departments
    LEFT SEMIJOIN @employees
    ON @departments.DepID == @employees.DepID;