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
