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]
