Antijoin

Some parts in the output code are omitted for clarity reasons.

Description

An antijoin returns rows from the left side of the predicate for which there are no corresponding rows on the right side of the predicate. It returns rows that fail to match (NOT IN) the subquery on the right side. Antijoin transformation cannot be done if the subquery is on an OR branch of the WHERE clause. (Oracle SQL Language Reference Anti Join).

No special transformation is performed for this kind of Join since Snowflake supports the same syntax.

Sample Source Patterns

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

Since the result set is too large, Row Limiting Clause was added. You can remove it to retrieve the entire result set.

Check this section to set up the sample database.

Where Not In

Oracle

Query

IN -> Oracle_01.sql

SELECT e.employee_id, e.first_name, e.last_name FROM hr.employees e
WHERE e.department_id NOT IN

    (SELECT h.department_id FROM hr.departments h WHERE location_id = 1700)
    
ORDER BY e.last_name
FETCH FIRST 10 ROWS ONLY;

Result

EMPLOYEE_ID|FIRST_NAME|LAST_NAME|
-----------+----------+---------+
        174|Ellen     |Abel     |
        166|Sundar    |Ande     |
        130|Mozhe     |Atkinson |
        105|David     |Austin   |
        204|Hermann   |Baer     |
        167|Amit      |Banda    |
        172|Elizabeth |Bates    |
        192|Sarah     |Bell     |
        151|David     |Bernstein|
        129|Laura     |Bissot   |

Snowflake

Query

OUT -> Oracle_01.sql

SELECT e.employee_id, e.first_name, e.last_name FROM
    hr.employees e
WHERE e.department_id NOT IN
        !!!RESOLVE EWI!!! /*** SSC-EWI-0108 - THE FOLLOWING SUBQUERY MATCHES AT LEAST ONE OF THE PATTERNS CONSIDERED INVALID AND MAY PRODUCE COMPILATION ERRORS ***/!!!
    (SELECT h.department_id FROM
            hr.departments h WHERE location_id = 1700)

ORDER BY e.last_name
    FETCH FIRST 10 ROWS ONLY;

Result

EMPLOYEE_ID|FIRST_NAME|LAST_NAME|
-----------+----------+---------+
        174|Ellen     |Abel     |
        166|Sundar    |Ande     |
        130|Mozhe     |Atkinson |
        105|David     |Austin   |
        204|Hermann   |Baer     |
        167|Amit      |Banda    |
        172|Elizabeth |Bates    |
        192|Sarah     |Bell     |
        151|David     |Bernstein|
        129|Laura     |Bissot   |

Where Not Exists

Oracle

Query

IN -> Oracle_02.sql

SELECT   d.department_id, d.department_name
FROM     hr.departments d
WHERE    NOT EXISTS
         
         (SELECT 1 FROM hr.employees E WHERE
         e.department_id = d.department_id)
         
ORDER BY d.department_id
FETCH FIRST 10 ROWS ONLY;

Result

DEPARTMENT_ID|DEPARTMENT_NAME     |
-------------+--------------------+
          120|Treasury            |
          130|Corporate Tax       |
          140|Control And Credit  |
          150|Shareholder Services|
          160|Benefits            |
          170|Manufacturing       |
          180|Construction        |
          190|Contracting         |
          200|Operations          |
          210|IT Support          |

Snowflake

Query

OUT -> Oracle_02.sql

SELECT   d.department_id, d.department_name
FROM
         hr.departments d
WHERE    NOT EXISTS
                  !!!RESOLVE EWI!!! /*** SSC-EWI-0108 - THE FOLLOWING SUBQUERY MATCHES AT LEAST ONE OF THE PATTERNS CONSIDERED INVALID AND MAY PRODUCE COMPILATION ERRORS ***/!!!
         (SELECT 1 FROM
                           hr.employees E WHERE
         e.department_id = d.department_id)

ORDER BY d.department_id
         FETCH FIRST 10 ROWS ONLY;

Result

DEPARTMENT_ID|DEPARTMENT_NAME     |
-------------+--------------------+
          120|Treasury            |
          130|Corporate Tax       |
          140|Control And Credit  |
          150|Shareholder Services|
          160|Benefits            |
          170|Manufacturing       |
          180|Construction        |
          190|Contracting         |
          200|Operations          |
          210|IT Support          |

Known issues

1. Results ordering mismatch between languages

The result of the query will have the same content in both database engines but the order might be different if no Order By clause is defined in the query.

Related EWIs

1. SSC-EWI-0108: This subquery matches a pattern considered invalid and may cause compilation errors.