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 an OR branch of the WHERE clause. (Oracle SQL Language Reference Semijoin Subsection)

Sample Source Patterns

Order by clause added because the result order may vary between Oracle and Snowflake.

Check this section to set up the sample database.

Basic Semijoin case

Oracle

Query

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;

Result

DEPARTMENT_ID|DEPARTMENT_NAME |MANAGER_ID|LOCATION_ID|
-------------+----------------+----------+-----------+
          110|Accounting      |       205|       1700|
           10|Administration  |       200|       1700|
           90|Executive       |       100|       1700|
          100|Finance         |       108|       1700|
           40|Human Resources |       203|       2400|
           60|IT              |       103|       1400|
           20|Marketing       |       201|       1800|
           70|Public Relations|       204|       2700|
           30|Purchasing      |       114|       1700|
           80|Sales           |       145|       2500|
           50|Shipping        |       121|       1500|

Snowflake

Query

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;

Result

DEPARTMENT_ID|DEPARTMENT_NAME |MANAGER_ID|LOCATION_ID|
-------------+----------------+----------+-----------+
          110|Accounting      |       205|       1700|
           10|Administration  |       200|       1700|
           90|Executive       |       100|       1700|
          100|Finance         |       108|       1700|
           40|Human Resources |       203|       2400|
           60|IT              |       103|       1400|
           20|Marketing       |       201|       1800|
           70|Public Relations|       204|       2700|
           30|Purchasing      |       114|       1700|
           80|Sales           |       145|       2500|
           50|Shipping        |       121|       1500|

As proved previously the semijoin in Oracle is functionally equivalent to Snowflake.

Known Issues

No issues were found.

Related EWIs

No related EWIs.