Anti join :

Anti join of two tables
Anti join
Principles of inner, outer joins revolve over the concept of selecting the matching records but if we need the opposite of this that is the records which do not have matching records then the concept of Anti join comes in the show. It is a reverse logic of join where we will choose rows from the left side of the predicate for which there is no match on the right i.e the behavior of ‘NOT IN‘ and ‘NOT EXISTS‘. ‘NOT IN‘ cannot handle null values in the subquery i.e if one value is null in the subquery then the entire NOT in will be false and no record will be returned but it is not true from ‘NOT EXISTS‘. Therefore we should use ‘NOT EXISTS‘ for anti join operation. In the left side diagram, the white portion of the circle shows the anti join of two tables A and B.

Anti join Examples (executable in oracle live sql):

select department_name,department_id from hr.departments dep
where not exists 
    (select 1 from hr.employees emp
    where emp.department_id=dep.department_id
    )
order by dep.department_name;


DEPARTMENT_NAMEDEPARTMENT_ID
Benefits160
Construction180
Contracting190
Control And Credit140
Corporate Tax130
Government Sales240
IT Helpdesk230
IT Support210
Manufacturing170
NOC220
Operations200
Payroll270
Recruiting260
Retail Sales250
Shareholder Services150
Treasury120


But if we use 'NOT IN' in that case we do not get any data.

select Department_name,department_id from hr.departments dep 
where department_id not in (select department_id from hr.employees );

no data found

This is happening as (select department_id from hr.employees ) is returning a null value and the entire query is evaluated as false. We can see it if we change the sql slightly like below and handle null values with the NVL function.

select Department_name,department_id from hr.departments dep 
where department_id not in (select NVL(department_id,0) from hr.employees )

now we are getting data:

DEPARTMENT_NAMEDEPARTMENT_ID
Treasury120
Corporate Tax130
Control And Credit140
Shareholder Services150
Benefits160
Manufacturing170
Construction180
Contracting190
Operations200
IT Support210
NOC220
IT Helpdesk230
Government Sales240
Retail Sales250
Recruiting260
Payroll270