Re: More grist for the PostgreSQL vs MySQL mill

Chad,

>> select * from table where table_id in (?, ?, ?, ?, ?, ?, ?, ...)

> > I usually try to rewrite this kind of queries to
> >
> > select whatever from table t1 join
> > (select table_id from xxxxx where xxxxx) t2 using (table_id)
> >
> >
>
Because the results would be different than a subselect, less work =
> faster. One thing to point out is that a query of the form:

Would normally result in a SORT UNIQUE for the "select id from bar where
> n=27" part. Where as:
>

select ...
> from foo f1, (select id from bar where n=27) f2
> where f1.id = f2.id
>
> is the same as...
>
> select ...
> from foo f1, bar f2
> where f2.n=27
> and f1.id=f2.id
>
> which would not result in a sort unique. In order to obtain the same
> results as a subselect you would need to group or distinct, and I would
> imagine the results would be the same as the IN..SUBSELECT
>

aaah, you are right. My rewriting only works when the "id" column is a
primary key in the subqueried table; that way guaranteed to be unique, so
that

select distinct id from whatever where whateverelse

yields the same results as

select id from whatever where whateverelse

thanks for pointing it out,

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
fx 01212-5-13695179
-
Python: the only language with more web frameworks than keywords.