Saturday, November 13, 2010

ORACLE FASTER NOT IN QUERY

Alternate to not in query
select table1.field1 from table1 where table1.field1 not in
(select table2.field1 from table2);
1st Rank using outer join:
SELECT table1.field1
FROM table1, table2
WHERE table1.field1 = table2.field1(+) and
table1.field1 is null;
2nd Rank using not exists:
SELECT table1.field1
FROM table1
WHERE NOT EXISTS (SELECT table2.field1 FROM table2 WHERE table1.field1 = table2.field1)
Enhanced by Zemanta

0 comments: