Re: Why Correlated Query vs. plain old Query

Ed Bruce (edward_at_igate1.hac.com) wrote:
> Can anyone explain to me the following SQL I got to work
> vs. the SQL I didn't.
 

> As a preface, I have two tables, A and B. Table A contains
> a foreign key to Table B's primary key, let's call it:
> A.B_pk. The question I wanted to answer is: What rows in
> table B are not referenced in table A. My approach to
> answering this was to ask the non-negative question first.
> What rows in table B are referenced in table A. Which I
> answered with the following SQL:
 

> select B.pk from B where B.pk in ( select A.B_pk from A );

This statement will work but would execute a lot faster if you did:

select B.pk from B where exists (select 1 from A where A.B_pk = B.pk);

In your case, the SQL engine would have to do an entire scan of table A for each row of table B. But by using exists, instead of in, the SQL engine can access table B using the primary index (if you have one), thus cutting down on access time.

> I then blindly used the negative of this to produce the
> following:
 

> select B.pk from B where B.pk not in ( select A.B_pk from
> A );

Even if this did work, it is ineficient because you are not correlating the subquery to the parent query.

> Which when executed returned no rows. The SQL that finally
> worked was:
 

> select B.pk from B b1 where B.pk not in
> ( select A.B_pk from A where b1.pk = A.B_pk );

Again, use NOT EXISTS instead of NOT IN
Always correlate your sub queries.
If table A had 1,000,000 rows in it and table B had 1,000,000 rows, your un-correlated queries would take "years" to run.

Peter Corrigan and Mark Gurry's book on ORACLE Performance Tuning has quite a bit in it about this subject and other performance tuning tips. Chapter 7 is about tuning SQL.

I highly recommend you read this book.

> What I don't understand is why the second select doesn't
> work and why I had to use the third select.

Always use NOT EXISTS in place of NOT IN.

> Ed Bruce

John.

--
 
John Blackburn                                          Phone: +61 7 2534634
jb2_at_qdot.qld.gov.au                                     Fax:   +61 7 8541194