List:General Discussion« Previous MessageNext Message »
From:Rhino Date:March 21 2006 10:03pm
Subject:Re: RIGHT JOIN better than INNER JOIN?
View as plain text  
Robert,

Your restatement of your original question uses "FULL JOIN" as if it means 
the same things as "INNER JOIN": that's simply not correct. A full join 
contains the results of an inner join PLUS the "orphan rows" from the 
right-hand table in the join PLUS the "orphan rows" from the left-hand table 
in the join. Furthermore, the last time I checked, which was probably at 
least a year ago now, MySQL didn't support a full join.

--
Rhino

----- Original Message ----- 
From: "Robert DiFalco" <rdifalco@stripped>
To: "Martijn Tonies" <m.tonies@stripped>; <mysql@stripped>
Sent: Tuesday, March 21, 2006 2:04 PM
Subject: RE: RIGHT JOIN better than INNER JOIN?


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




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1


-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.6/286 - Release Date: 20/03/2006




-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.6/286 - Release Date: 20/03/2006

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