[freetds] SQL Query troubles

Michael Peppler mpeppler at peppler.org
Thu Jan 8 14:18:42 EST 2004


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



More information about the FreeTDS mailing list