Re: Why Correlated Query vs. plain old Query

From: Lawrence V. Rohrer <lrohrer_at_earthlink.net>
Date: 17 Feb 95 05:34:17 GMT
Message-ID: <lrohrer-1602952134170001_at_lrohrer.earthlink.net>


In article <3i0ogo$pe1_at_hacgate2.hac.com>, 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.
>
> select B.pk from B where B.pk not in ( select A.B_pk from
> A );

The inner SQL will fire first returning all of the rows in table A. If we assume that this is real data -- There are many values of B.pk and many of A.B_pk then at least one record returned from the inner query will not match the outer query. << B.pk.1 != B.pk.2 >>

  • for faster performance try: select B.pk from B b1 where not exists (select 'x' from A where a.B_pk = b1.pk)

With your third statement and the one above the tables are joined. Received on Fri Feb 17 1995 - 06:34:17 CET

Original text of this message