Anti join in oracle

Anti join :

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_NAME	DEPARTMENT_ID
Benefits	160
Construction	180
Contracting	190
Control And Credit	140
Corporate Tax	130
Government Sales	240
IT Helpdesk	230
IT Support	210
Manufacturing	170
NOC	220
Operations	200
Payroll	270
Recruiting	260
Retail Sales	250
Shareholder Services	150
Treasury	120

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_NAME	DEPARTMENT_ID
Treasury	120
Corporate Tax	130
Control And Credit	140
Shareholder Services	150
Benefits	160
Manufacturing	170
Construction	180
Contracting	190
Operations	200
IT Support	210
NOC	220
IT Helpdesk	230
Government Sales	240
Retail Sales	250
Recruiting	260
Payroll	270