IN vs EXIST vs JOIN

  • Most Oracle IN clause queries involve a series of literal values, and when a table is present a standard join is better. 
  • In most cases the Oracle cost-based optimizer will create an identical execution plan for IN vs EXISTS, so there is no difference in query performance.
  • The Exists keyword evaluates true or false, but the IN keyword will compare all values in the corresponding subquery column.
  • If you are using the IN operator, the SQL engine will scan all records fetched from the inner query. On the other hand, if we are using EXISTS, the SQL engine will stop the scanning process as soon as it found a match.

Important point to be noted for IN and Exist

  • The EXISTS clause is much faster than IN when the subquery results is very large.
  • Conversely, the IN clause is faster than EXISTS when the subquery results is very small.
  • IN clause can’t compare anything with NULL values, but the EXISTS clause can compare everything with NULLs.

Note : DB Dump at the bottom of this page

IN Query Example :

select * from EMPLOYEE_2020108119 where DEPT_ID in (select DEPT_ID from DEPARTMENT_2020108119);
select * from EMPLOYEE_2020108119 where DEPT_ID not in (select DEPT_ID from DEPARTMENT_2020108119); --null department are not displayed in

EXISTS Query Example : 

select * from EMPLOYEE_2020108119 e where exists (select 1 from DEPARTMENT_2020108119 d where e.dept_id=d.dept_id);
select * from EMPLOYEE_2020108119 e where not exists (select 1 from DEPARTMENT_2020108119 d where e.dept_id=d.dept_id);--null departments are displayed here

Join Query Example :

select e.* from EMPLOYEE_2020108119 e join DEPARTMENT_2020108119 d on e.dept_id=d.dept_id;
select e.* from EMPLOYEE_2020108119 e join DEPARTMENT_2020108119 d on e.dept_id=d.dept_id;

Dump for Example : 

create table EMPLOYEE_2020108119 (
    EMP_ID number(10,0) not null,
    END_DATE date,
    FIRST_NAME varchar2(20 char) not null,
    LAST_NAME varchar2(20 char) not null,
    START_DATE date not null,
    TITLE varchar2(20 char),
    ASSIGNED_BRANCH_ID number(10,0),
    DEPT_ID number(10,0),
    SUPERIOR_EMP_ID number(10,0)
);

create table DEPARTMENT_2020108119 (
    DEPT_ID number(10,0) not null,
    NAME varchar2(20 char) not null
);


insert into DEPARTMENT_2020108119 (dept_id, name) values (1, 'Operations');
insert into DEPARTMENT_2020108119 (dept_id, name) values (2, 'Loans');
insert into DEPARTMENT_2020108119 (dept_id, name) values (3, 'Administration');
insert into DEPARTMENT_2020108119 (dept_id, name) values (4, 'IT');
insert into DEPARTMENT_2020108119 (dept_id, name) values (5, 'HR');

insert into EMPLOYEE_2020108119 (emp_id, First_Name, Last_Name, start_date,dept_id, title, assigned_branch_id) 
values (1, 'Michael', 'Smith', to_date('2001-06-22','yyyy-MM-dd'), (select dept_id from department where name = 'Administration'),'President',(select branch_id from branch where name = 'Headquarters'));

insert into EMPLOYEE_2020108119 (emp_id, First_Name, Last_Name, start_date, dept_id, title, assigned_branch_id)
values (2, 'Susan', 'Barker',to_date( '2002-09-12','yyyy-MM-dd'),(select dept_id from department where name = 'Administration'), 'Vice President',(select branch_id from branch where name = 'Headquarters'));

insert into EMPLOYEE_2020108119 (emp_id, First_Name, Last_Name, start_date, dept_id, title, assigned_branch_id)
values (3, 'Robert', 'Tyler',to_date( '2000-02-09','yyyy-MM-dd'),(select dept_id from department where name = 'Administration'),'Treasurer',(select branch_id from branch where name = 'Headquarters'));

insert into EMPLOYEE_2020108119 (emp_id, First_Name, Last_Name, start_date,dept_id, title, assigned_branch_id)
values (4, 'Susan', 'Hawthorne',to_date( '2002-04-24','yyyy-MM-dd'),(select dept_id from department where name = 'Operations'),'Operations Manager',(select branch_id from branch where name = 'Headquarters'));

insert into EMPLOYEE_2020108119 (emp_id, First_Name, Last_Name, start_date,dept_id, title, assigned_branch_id)
values (5, 'John', 'Gooding',to_date( '2003-11-14','yyyy-MM-dd'),(select dept_id from department where name = 'Loans'), 'Loan Manager',(select branch_id from branch where name = 'Headquarters'));

insert into EMPLOYEE_2020108119 (emp_id, First_Name, Last_Name, start_date,dept_id, title, assigned_branch_id)
values (6, 'Tyson', 'Gill',sysdate-10,0, 'Loan Manager',0);

insert into EMPLOYEE_2020108119 (emp_id, First_Name, Last_Name, start_date,dept_id, title, assigned_branch_id)
values (7, 'Justin', 'Gill',sysdate-6,null, 'Loan Manager',0);

select * from EMPLOYEE_2020108119;
select * from DEPARTMENT_2020108119;

Leave a Comment