Why Correlated Query vs. plain old Query

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 );

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 );

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 );

What I don't understand is why the second select doesn't work and why I had to use the third select. Any enlightenment is much appreciated. I can not swear that the second SQL would work on other RDBMS, but I do believe I have structured queries like this on VAX RDB, Ingres, and Sybase. Though it has been a few years since I last used Ingres or Sybase. This is with Oracle 6.0 running under SGI IRIX 4.0.1, Version 6.2.0.

Ed Bruce Received on Fri Feb 17 1995 - 00:52:56 CET