Monday 5 February 2007

SQL Replacing a NOT IN with an Outer Join

I came across this code while searching through the "Oracle 10g Complete Reference" by Kevin Loney. It provides a much faster approach to finding out records that belonging to one table and not to an other.

So the query:
select TITLE
from BOOKSHELF
where TITLE not in (Select TITLE from BOOKSHELF_CHECKOUT)
order by TITLE;
can be replaced by
select distinct B.TITLE
from BOOKSHELF_CHECKOUT BC right outer join BOOKSHELF B
     on BC.TITLE = B.TITLE
where BC.TITLE = NULL
order by TITLE;

No comments :