Re: NOT IN is very inefficient

[Following up my own post...]

In article <6vi8r9$bd9$1_at_nnrp1.dejanews.com>, jamesc7704_at_my-dejanews.com wrote:
| In article <360ce23e.31649562_at_newslist>, skoterski_at_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. Received on Sat Oct 10 1998 - 00:00:00 CDT