Oracle Database JOINS SEMIJOIN
Example
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 n times.
