A semijoin query can be used, for example, to find all departments with at least one employee whose salary exceeds 2500.
SELECT * FROM departments WHERE EXISTS (SELECT 1 FROM employees WHERE departments.department_id = employees.department_id AND employees.salary > 2500) ORDER BY department_name;
This is more efficient than the full join alternatives, as inner joining on employees then giving a where clause detailing that the salary has to be greater than 2500 could return the same department numerous times. Say if the Fire department has
n employees all with salary 3000,
select * from departments, employees with the necessary join on ids and our where clause would return the Fire department