MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Frederic Wenzel Date:November 6 2004 11:56am
Subject:Re: Advanced SELECT Syntax Help Needed!
View as plain text  
On Fri, 5 Nov 2004 17:49:29 -0500, Rhino <rhino1@stripped> wrote:
> I'm not sure why you want to use a subquery; if MySQL is anything like DB2,
> a join usually performs better than a subquery and the optimizer converts a
> subquery to a join ("under the covers") whenever it can anyway. Therefore,
> how about something like:
> 
> select id, name, linkname1, linkname2
> from main m right outer join links1 l1 on m.id = l1.id
> right outer join links l2 on m.id = l2.id;

Yes, indeed joins usually perform better than subselects.
I agree to your suggested query, but I think we ought to use LEFT
joins here rather than RIGHT joins because Monique wants to receive
any main record regardless of the existence of corresponding "links".

So the query I would use is:

SELECT m.id, l1.linkname1, l2.linkname2
FROM main m LEFT JOIN links1 l1 ON (m.id = l1.id)
LEFT JOIN links2 l2 ON (m.id = l2.id);

Please let us know if it worked, Monique.

Regards
Fred
Thread
Advanced SELECT Syntax Help Needed!Monique5 Nov
  • Re: Advanced SELECT Syntax Help Needed!Rhino5 Nov
    • Re: Advanced SELECT Syntax Help Needed!Frederic Wenzel6 Nov
      • RE: Advanced SELECT Syntax Help Needed!Monique7 Nov