Hi Maran Firstly

It’s not about the difference in performance only because they are not the same when dealing with nulls in the data.

for example, we want to check how many employees are not managers of other employees by using NOT IN

SELECT COUNT (*)
FROM EMPLOYEES
WHERE EMPLOYEE_ID NOT IN (SELECT MANAGER_ID FROM EMPLOYEES);

the result will be :

COUNT(*)
----------
0

This means all employees are managers in this case.

let’s then take the same example but using NOT EXISTS

SELECT COUNT (*)
FROM EMPLOYEES EMP_1
WHERE NOT EXISTS
(SELECT NULL
FROM EMPLOYEES EMP_2
WHERE EMP_2.MANAGER_ID = EMP_1.EMPLOYEE_ID);

the result will be :

COUNT(*)
----------
89

So here, out of 101 employees, there are 89, not managers.

The most crucial thing is NULL represented in the manager_id column for the first employee 101 (Steven King). So wherever null is there while using NOT IN or IN, the evaluation of the inner query will be either FALSE or NULL and will return no records.

So now, when it comes to performance, it depends on the amount of data that both subquery and the outer query returns. If they are small, then IN is typically more appropriate. And vise versa. But remember, we have assumed that there are no nulls in the subquery result.

Regards

Hello Chandra,

To iterate through a VO in OAF by either RowSetIterator and then loop through it to fetch attributes value, or using FilteredRows this will give us an advantage of getting only rows that meets a particular condition, or using RowQualifier this is similar to  FilteredRows but instead of typing the condition using VO attribute, we are write normal SQL condition.

Now let’s see an example using the first option:

OAApplicationModule oaapplicationmodule = pageContext.getApplicationModule(webBean);
DeptEOVOImpl deptEOVO = 
(DeptEOVOImpl)oaapplicationmodule.findViewObject("DeptEOVO");

DeptEOVORowImpl deptEOVORow = null;

RowSetIterator deptRowSetIter = deptEOVO.createRowSetIterator("DeptRowSetIter");

while(deptRowSetIter.hasNext())
{
deptEOVORow = (DeptEOVORowImpl)deptRowSetIter.next();
System.out.println("DeptID-->"+ deptEOVORow.getDeptId() + " Dept Name-->"+ deptEOVORow.getDeptName());

}
deptRowSetIter.closeRowSetIterator();