The DISTINCT is required because there can be multiple records Loc table.

Speed isn't a real issue (with 40K records in Pol and 200K records in Loc the query executes in 3/4 of a second). Even if it went up to 10 seconds you'ld never notice because the entire process takes about 10 minutes to complete.

Thanks to you, Craig, and Sergey for your responses.


>>Is it better to use a JOIN or a SUBSELECT if either will produce the correct result?
>>
>>For example

>>SELECT DISTINCT pol.agentcode,pol.ctrl_num,pol.incep_date FROM frpolicy pol;
>>  JOIN frlocat loc ON pol.agentcode=loc.agentcode and pol.ctrl_num=loc.ctrl_num;
>>  WHERE pol.incep_date >={8/31/2005} AND loc.state='OR'
>>
>>
>>SELECT pol.agentcode,pol.ctrl_num,pol.incep_date FROM frpolicy pol;
>> WHERE EXISTS (SELECT * FROM frlocat loc;
>>                WHERE loc.agentcode=pol.agentcode
>>                  AND loc.ctrl_num=pol.ctrl_num;
>>                  AND loc.state="OR")
>>   AND pol.incep_date >= {8/1/2005}
>>

>>
>>produce identical results in approximately the same time.
>>
>>Is it a matter of style or is there a reason to use one rather than the other?
>>
>>Thanks......
>
>If DISTINCT is necessary, then:
>The second it is formally (in relational algebra) more correct,
>infact the second can to be very more faster ( best case ) but little slower ( worst case )