For me the argument is a little pedantic. The contract of the descriptor table is that it must reference a name; there is code and constraints to enforce this. I am happy to have the query return nulls to indicate a programming error that can be quickly addressed. _If_ (after buffer tuning et al) a RIGHT JOIN still provides a substantial performance improvement over a FULL JOIN in this case, my customers would want me to provide that rather than have me tell them it is an "inappropriate join" or that I am asking the database server developers to improve their query optimizer.
I wasn't really looking to get into a philosophical debate on correctness so let me restate my question a little better. Is there a reason in MySQL/InnoDB why a RIGHT JOIN would substantially out perform a FULL JOIN in those cases where the results would be identical? It is a little difficult to test query performance empirically since performance will change as different indices are swapped in and out of memory buffers and such (although I have turned query caching off), but it appears that for a table with 1-2 million rows a query similar to what I posted here was faster with a RIGHT JOIN. R. -----Original Message----- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 21, 2006 9:43 AM To: mysql@lists.mysql.com Subject: Re: RIGHT JOIN better than INNER JOIN? Robert, > 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. Gee, I wonder why ... This happens to be the query that returns the rows as it should. What happens, if two years from now you didn't document WHY you wrote a "right join" query instead of an inner join and someone figures out that this could return nulls for a result column? If you start using the wrong joins, you will make things harder on yourself and others. As I said: if performance isn't satisfactory (which sounds a bit strange for this situation), then try to solve that. Either by using different index/buffer/caching strategies or by complaining to the people who created the database system in the first place. 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
