MySQL Forums
Forum List  »  Optimizer & Parser

Re: INNER JOIN (WHERE) vs LEFT JOIN
Posted by: Sergey Petrunya
Date: November 03, 2005 05:26AM

> Secondly, compare the two SQLs:
> SELECT * FROM Table1, Table2 WHERE Table1.columnA
> = Table2.columnB;
> and
> SELECT * FROM Table1 LEFT JOIN Table2 ON
> Table1.columnA = Table2.columnB;
>
> Which would be executed faster? I understand the 2
> SQLs have different function. LEFT JOIN should
> used for scenario if not all record of
> Table1.columnA exist in Table2.columnB. But
> assuming it does, which query would be faster?

It depends on if there are indexes on columnA or columnB and which index is better etc. In general, the optimizer will have more options for executing inner join than for outer join, so inner join has a chance of being executed faster.

The execution of nested joins is described in details here:
http://dev.mysql.com/doc/refman/4.1/en/left-join-optimization.html
http://dev.mysql.com/doc/refman/5.0/en/nested-joins.html

Sergey Petrunia, Software Developer
MySQL AB, www.mysql.com
My blog: http://s.petrunia.net/blog

Options: ReplyQuote


Subject
Views
Written By
Posted
88410
November 02, 2005 08:12PM
24982
November 03, 2005 05:18AM
Re: INNER JOIN (WHERE) vs LEFT JOIN
24802
November 03, 2005 05:26AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.