ERPstuff - Oracle EBS Community

Can nay one tell me that Sql Tuning

what is IN,
NOT IN and
EXISTS,
NOT EXISTS
where are the about 4 terms we may use?
If possible with examples.
have a nice day

Hi

Here are few examples to grasp some idea:

select DEPTNO from EMP
where (DEPTNO=10) or (DEPTNO=20);

Same result could be obtain With IN or NOT IN:

select DEPTNO from EMP
where DEPTNO in(10,20);

Likewise the use of EXISTS or NOT EXISTS must return TRUE if at least one condition is met. We want to check which DEPTNO has no employee.

select d.DEPTNO from dept d
where not exists
(select e.DEPTNO from EMP e where d.DEPTNO=e.DEPTNO);

Hope that would be helpfull to you.

Thanks
Amir