Oracle Antijoins

What is Antijoins in Oracle?

An antijoin between two tables returns rows from the first table where no matches are found in the second table. Anti-joins are written using the NOT EXISTS or NOT IN constructs. An anti-join is essentially the opposite of a semi-join. To find un-matched data between two tables Anti-Join is better than using not in or not exists.

When to use an Anti-Join?

• Anti-Joins are only available when performing a NOT IN sub-query
• If the subquery of "NOT IN" results at least one NULL value then entire NOT IN will be false and no results will return, But "NOT EXIST" will consider NULL as value and returns the value.
• The sub-query must not be correlated. ie. The sub-query cannot contain any expressions in the SELECT or WHERE clauses that refer to columns in the outer query.
• Use anti-joins when a lot of rows are being selected in the outer query.

Sample table: employees

Sample table: departments

Example: Oracle antijoins without using antijoins

The following example returns a list of empty departments.

SELECT   D1.department_id, D1.department_name
      FROM     departments D1
      MINUS
      SELECT   D2.department_id, D2.department_name
      FROM     departments D2, employees E2
      WHERE    D2.department_id = E2.department_id
      ORDER BY 1;

Sample Output:

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
          220 NOC
          230 IT Helpdesk
          240 Government Sales
          250 Retail Sales
          260 Recruiting
          270 Payroll

16 rows selected.

Example: Oracle antijoins using antijoins

Here is the code of the above example with using antijoins. Here we will get the same result.

SELECT   D.department_id, D.department_name
FROM     departments D
WHERE    NOT EXISTS
         (
         SELECT 1
         FROM   employees E
         WHERE  E.department_id = D.department_id
         )
ORDER BY D.department_id;

Sample Output:

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
          220 NOC
          230 IT Helpdesk
          240 Government Sales
          250 Retail Sales
          260 Recruiting
          270 Payroll

16 rows selected.

Example - 2:

The following example selects a list of employees with employee ID, first name, last name and job ID columns, who are not in a particular set of departments:

SELECT employee_id,first_name,last_name,job_id
FROM employees 
   WHERE department_id NOT IN 
   (SELECT department_id FROM departments 
       WHERE location_id = 1700);
	   

Sample Output:

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 JOB_ID
----------- -------------------- ------------------------- ----------
        202 Pat                  Fay                       MK_REP
        201 Michael              Hartstein                 MK_MAN
        107 Diana                Lorentz                   IT_PROG
        106 Valli                Pataballa                 IT_PROG
        105 David                Austin                    IT_PROG
        104 Bruce                Ernst                     IT_PROG
        103 Alexander            Hunold                    IT_PROG
        204 Hermann              Baer                      PR_REP
        179 Charles              Johnson                   SA_REP
        177 Jack                 Livingston                SA_REP
        176 Jonathon             Taylor                    SA_REP
        175 Alyssa               Hutton                    SA_REP
        174 Ellen                Abel                      SA_REP
        173 Sundita              Kumar                     SA_REP
        172 Elizabeth            Bates                     SA_REP
        171 William              Smith                     SA_REP
        170 Tayler               Fox                       SA_REP
        169 Harrison             Bloom                     SA_REP
        168 Lisa                 Ozer                      SA_REP
        167 Amit                 Banda                     SA_REP
        166 Sundar               Ande                      SA_REP
        165 David                Lee                       SA_REP
		........

Previous: SEMIJOINS
Next: Oracle Home