Semijoins and Antijoins

Contact Us or call 1-877-932-8228
Semijoins and Antijoins

Semijoins and Antijoins

A semi-join returns rows from a table where matches are found in the joined table. It uses the IN or EXISTS operators. An anti-join returns rows from a table for which there are no corresponding rows in the joined table. It uses the NOT IN or NOT EXISTS keywords.

For example, list all of the job ids for all of the jobs that have ever been assigned to an employee.

Code Sample:

Joining-Tables/Demos/semijoin.sql
SELECT * 
FROM jobs 
WHERE job_id IN (
	SELECT job_id 
	FROM job_history);

This example is essentially the same as an inner query. However, it provides a different type of flexibility unavailable to an inner join by adding criteria to the WHERE clause of the subquery.

Now list all of the job ids for all of the jobs that have never been assigned to an employee.

Code Sample:

Joining-Tables/Demos/antijoin.sql
SELECT * 
FROM jobs 
WHERE job_id NOT IN (
	SELECT job_id 
	FROM job_history
);

This is simply the inverse of the previous example.

Next