From: jamesc@zip.com.au (James Cribb)
Subject: Re: NOT IN is very inefficient
Date: 1998/10/10
Message-ID: <6vma5n$qnb$1@the-fly.zip.com.au>#1/1
References: <6uefph$7ii$1@nnrp1.dejanews.com> <360ce23e.31649562@newslist>
Organization: The Zipsters
Newsgroups: comp.databases


[Following up my own post...]

In article <6vi8r9$bd9$1@nnrp1.dejanews.com>, jamesc7704@my-dejanews.com wrote:
| In article <360ce23e.31649562@newslist>, skoterski@NOSPAMinprise.com wrote:
| >
| >   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.

Mea culpa.  The query above DOES work in SQL Anywhere.
I had not seen the above syntax for outer joins before
and was instead using the syntax I was familiar with.

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

This too was a misteak.  It actually returns

   A.i  B.i
     1  1
     2  (null)
     3  3

as it should.  However...

| Filtering "B.i is null" returns all three rows of A.

does produce the unexpected result

   A.i  B.i
     1  (null)
     2  (null)
     3  (null)

| Is this a bug in SQL Anywhere?

Perhaps, but I now know the work-around.