List:General Discussion« Previous MessageNext Message »
From:Rhino Date:March 21 2006 5:06am
Subject:Re: RIGHT JOIN better than INNER JOIN?
View as plain text  
The only relational databases I've ever used to any significant extent are 
MySQL and DB2. I've used DB2 for a lot longer than MySQL and on most of the 
platforms on which it runs over various versions. As far as I'm concerned, 
the answer to your questions, at least as far as DB2 goes, is: it depends.

It depends on a host of factors. In no particular order, these factors 
include:
- which version of DB2 you are using
- what hardware you are running on
- how you write your SQL
- whether the data is properly clustered
- whether the tables and indexes have been reorganized in a timely fashion
- etc. etc.

You simply can't make a categorical statement that a right join will perform 
better than an inner join - or vice versa - in every case in DB2. All 
versions of DB2 use a cost-based optimizer that makes great efforts to give 
the optimum access path (and therefore optimum performance) for each query. 
A lot of very smart people have worked on the design of that optimizer over 
the years - I've met some of them - but, as good as the DB2 optimizer is, it 
can still make inappropriate decisions. This happens when you don't do 
routine maintenance like reorganizing tables and the RUNSTATS utility but 
the way you write (or mis-write) your SQL can also affect your access path 
and therefore your performance.

This unpredictability may sound like a bad thing but it is often a very good 
thing since the optimizer has many "tricks" and shortcuts. It will often 
rewrite a poorly-written query to improve its performance.

You may be able to find more categorical answers for the other major 
databases, like Oracle, since they tend to use different optimizer designs.

The only way to be really sure though is to do a proper benchmark for all 
the platforms and configurations that interest you.

--
Rhino

----- Original Message ----- 
From: "Robert DiFalco" <rdifalco@stripped>
To: <mysql@stripped>
Sent: Monday, March 20, 2006 7:11 PM
Subject: RIGHT JOIN better than INNER JOIN?


I apologize if this is a naive question but it appears through my
testing that a RIGHT JOIN may out perform an INNER JOIN in those cases
where they would produce identical result sets. i.e. there are no keys
in the left table that do not exist in the right table.

Is this true? If so, it this peculiar to MySQL or would this be true
with almost all database servers? i.e. Oracle, DB2, MSSQL, etc.

TIA,

R.


-- 
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.5/284 - Release Date: 17/03/2006




-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 17/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