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?
... 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.
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).