FreeTDS Development Group

[Michael Peppler <mpeppler AT peppler.org>]

On Thu, 2004-01-08 at 11:08, Lowden, James K wrote:
> > From: Steven Orton [mailto:sorton9999 AT usa.net]
> > Sent: January 8, 2004 1:57 PM
> > 
> >   I seem to be having problems with the following query:
> > 
> >   SELECT * FROM table1 WHERE Target_ID=(SELECT DISTINCT 
> > Target_ID FROM table2
> > WHERE PlatformTypeID=62 AND db_ctx_id=3)
> 
> It's not a good idea to rely on a subquery returning only one row to a point
> evaluation.  How do you expect the server to cope if it returns two
> (distinct) Target_IDs?  
> 
> I would do:
> 
> SELECT * FROM table1 
> WHERE Target_ID in (
>       SELECT DISTINCT Target_ID 
>       FROM table2
>       WHERE PlatformTypeID=62 
>       AND db_ctx_id=3
>       )

In general using a join rather than a sub-query gets better performance:

select * 
  from table1 t1
     , table2 t2
 where t1.TargetID = t2.TargetID
   and t2.PlatformTypeID = 62
   and t2.db_ctx_id = 3

Michael
-- 
Michael Peppler                              Data Migrations, Inc.
mpeppler AT peppler.org                 http://www.mbay.net/~mpeppler
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or 
long term contract positions - http://www.mbay.net/~mpeppler/resume.html