MySQL Forums
Forum List  »  Newbie

Re: nested select
Posted by: Rick James
Date: February 25, 2010 04:50PM

The COUNT(*) works better as a subquery.

WHERE id IN ( SELECT ... ) -- is almost always better optimized via a JOIN.

SELECT DISTINCT(id) WHERE id IN (... UNION ...)
-->
SELECT id WHERE id IN (... UNION DISTINCT ...)
-->
JOIN (... UNION DISTINCT ...) x ON a.id = x.id

I agree with
"Never use a subquery where a join will do."
but have the following exception:
"A subquery may well be more efficient IF it does some kind of aggregation (COUNT, GROUP BY, LIMIT, etc)."

IN ( SELECT ... ) -- rarely optimal.

FROM ( SELECT ... ) x -- a form of subquery that is sometimes overlooked.

Options: ReplyQuote


Subject
Written By
Posted
February 22, 2010 08:37AM
February 22, 2010 09:19AM
February 22, 2010 11:07AM
Re: nested select
February 25, 2010 04:50PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.