[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