List:General Discussion« Previous MessageNext Message »
From:Robert DiFalco Date:March 21 2006 5:37pm
Subject:RE: RIGHT JOIN better than INNER JOIN?
View as plain text  
Martjin,

Of course one should use the right JOIN for the job. But let me ask you,
which join would you use here?

You have a table called Descriptors, it has a field called nameID which
is a unique key that relates to a Names table made up of a unique
identity and a VARCHAR name. I think most people would write a simple
query like this:

   SELECT desc.<fields>, names.name 
   FROM desc JOIN names ON desc.nameId = names.Id
   ORDER BY names.name

However, it this really correct? Every descriptor has a record in names,
so it could be equivalently written as:

   SELECT desc.<fields>, names.name 
   FROM desc RIGHT JOIN names ON desc.nameId = names.Id
   ORDER BY names.name

My guess is that most people conventionally write the first query. 

R.
 

-----Original Message-----
From: Martijn Tonies [mailto:m.tonies@stripped] 
Sent: Tuesday, March 21, 2006 1:01 AM
To: mysql@stripped
Subject: Re: RIGHT JOIN better than INNER JOIN?




> I apologize if this is a naive question but it appears through my 
> testing that a RIGHT JOIN may out perform an INNER JOIN in those cases

> where they would produce identical result sets. i.e. there are no keys

> in the left table that do not exist in the right table.
> 
> Is this true? If so, it this peculiar to MySQL or would this be true 
> with almost all database servers? i.e. Oracle, DB2, MSSQL, etc.

Use the join that is appropriate for your query, do not use a different
one.

If speed isn't OK, then bug the guys that do the implementation, but
don't start changing the query that _might_ return a different result
if, for example, someone else starts working at the application and
figures "hey, this is a right join, so it's optional" etc ...

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1



Thread
RIGHT JOIN better than INNER JOIN?Robert DiFalco21 Mar
  • Re: RIGHT JOIN better than INNER JOIN?Rhino21 Mar
  • Re: RIGHT JOIN better than INNER JOIN?Jochem van Dieten21 Mar
  • Re: RIGHT JOIN better than INNER JOIN?Martijn Tonies21 Mar
  • Re: RIGHT JOIN better than INNER JOIN?Rhino21 Mar
  • RE: RIGHT JOIN better than INNER JOIN?Nicolas Verhaeghe21 Mar
RE: RIGHT JOIN better than INNER JOIN?Robert DiFalco21 Mar
  • Re: RIGHT JOIN better than INNER JOIN?Martijn Tonies21 Mar
RE: RIGHT JOIN better than INNER JOIN?Robert DiFalco21 Mar
  • Re: RIGHT JOIN better than INNER JOIN?gerald_clark21 Mar
  • Re: RIGHT JOIN better than INNER JOIN?Rhino21 Mar