List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:March 21 2006 5:43pm
Subject:Re: RIGHT JOIN better than INNER JOIN?
View as plain text  
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
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