A query statement, I have, can be written using subqueries. If subquering are employed, they will query different fields of the same table along the query statement. Will the job can be done more effectively to have two queries instead of one query statement and use the first query to fetch data and pass it into the second query?

that depends on the queries

i wonder what they look like


rudy

Thanks Andy for your interests.

The query is something like the followings:

... AND (EXISTS (SELECT 1 FROM preference_type_table WHERE userid=p.userid AND type IN (SELECT type FROM profile_table WHERE userid='Tom') OR NOT EXISTS (SELECT 1 FROM preference_type_table WHERE userid=pf.userid) )

AND (EXISTS (SELECT 1 FROM preference_plan_table WHERE userid=p.userid AND plan IN (SELECT plan FROM profile_table WHERE userid='Tom') ) OR NOT EXISTS (SELECT 1 FROM "preference_plan_table WHERE userid=pf.userid) )

AND EXISTS (SELECT userid FROM preference_table WHERE p.userid=userid AND (2003 - (SELECT year FROM profile_table WHERE userid = 'Tom')) BETWEEN minage AND maxage) )

where the three subqueries

SELECT type FROM profile_table WHERE userid='Tom'

SELECT plan FROM profile_table WHERE userid='Tom'

SELECT year FROM profile_table WHERE userid = 'Tom'

are what I mentioned in my posting whether shall be obtained thorough a separated query, but not in this subquery form.

Thanks again for your advice.

v.

if they run fine, good performance, then leave them alone

i think some of the double EXISTS (if a preference exists and it's one of his, or if a preference doesn't exist) could be replaced by a left outer join

but don't rewrite the queries unless you have a real performnace problem

because then you may end up in a redesign, too

i can't really tell without seeing your data

like i said, if your queries run okay, leave them


rudy

I raise this issue when I modified the query yesterday to add the type and plan as search criteria. I guess I shall use a common sense using a separated query to fetch data when the number of the subqueries is high.

Thanks Rudy for you opinion (sorry for reading your name wrong. I just got up from bed).

v.

Rudy,

Also, I take a look at your suggestion that using left outer join instead of EXISTS in the subqueries. How to using outer join in different levels' query?