Semijoin
Description
A semijoin returns rows that match an
EXISTS
subquery without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery. Semijoin transformation cannot be done if the subquery is on anOR
branch of theWHERE
clause. (Oracle SQL Language Reference Semijoin Subsection)
Sample Source Patterns
Basic Semijoin case
Oracle
SELECT * FROM hr.departments
WHERE EXISTS
(SELECT * FROM hr.employees
WHERE departments.department_id = employees.department_id
AND employees.salary > 2500)
ORDER BY department_name;
Snowflake
SELECT * FROM
hr.departments
WHERE EXISTS
(SELECT * FROM
hr.employees
WHERE departments.department_id = employees.department_id
AND employees.salary > 2500)
ORDER BY department_name;
Known Issues
No issues were found.
Related EWIs
No related EWIs.
Last updated