Anti join :
![]() |
Anti join |
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 |
0 Comments
Post a Comment