List:General Discussion« Previous MessageNext Message »
From:Robert DiFalco Date:March 21 2006 7:04pm
Subject:RE: RIGHT JOIN better than INNER JOIN?
View as plain text  
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:m.tonies@stripped] 
Sent: Tuesday, March 21, 2006 9:43 AM
To: mysql@stripped
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/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