Daniel Rail wrote:

> Hi,
>
> At April 16, 2003, 09:53, sivram_mail wrote:
>
>
>>this is the query which is written:
>>-----------------------------------
>>select * from table1 where id not in
>>(select id from table2,table1 where table1.no=table2.no )
>
>
>>In this query , there is no join condition for primary key.
>
>
> Try:
>
> select * from table1 where not exists
> (select * from table2 where table2.no=table1.no )
>

to Sivram:
You may even take it 1 step further:

select * from table1 where not exists
(select 1 from table2 where table2.no=table1.no )
^^^

selecting "1" may save the DBMS the trouble of actually fetching the datapage (maybe not),
and it has the slight benefit of making it clear that you have no actual interest in the data
returned from the subquery.


You could also try:

select table1.* from
table1 left outer join table2 on table1.no = table2.no
where table2.no is null

if you want to avoid a subselect. (although I think the subselect in this specific case is fine as long as you have an index)

Hope that may be helpful.

David Z