Re: NOT IN is very inefficient

In article <360ce23e.31649562_at_newslist>,   skoterski_at_NOSPAMinprise.com wrote:
> On Thu, 24 Sep 1998 22:06:41 GMT, bjohnsto_usa_net_at_my-dejanews.com wrote:
>
> >SELECT my_field FROM my_table1
> >WHERE my_field NOT IN
> > (SELECT my_field FROM my_table2)
> >
> >Oracle (v7.3.4) is doing a table scan of the my_table2 (and my_table1) even
> >though there is an index on table2. Performance is terrible.
>
> What is you do an outer join, filter the result set to just those rows
> where the second table's key column contains NULL, and leave all of the
> second table's columns out of the SELECT clause?
>
> SELECT T1.my_field
> FROM my_table1 T1
> LEFT OUTER JOIN my_table2 T2
> ON (T1.my_field = T2.my_field)
> WHERE (T2.my_field IS NULL)

I tried this in Sybase SQL Anywhere, but it didn't work.

Table A (i int) has values 1, 2, 3.
Table B (i int) has values 1, 3

  select A.i, B.i from A, B where A.i *= B.i

("*=" means "left outer join") returns

  A.i B.i

    1  (null)
    2  (null)
    3  (null)

Filtering "B.i is null" returns all three rows of A. Is this a bug in SQL Anywhere?
Is there another way to write the query?

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Oct 08 1998 - 00:00:00 CDT