Is Left Join faster than Inner Join?

e,g,

Select * From TableA LEFT JOIN TableB ON xxx

vs

Select * From TableA JOIN TableB ON xxx


Since I believe LEFT JOIN requiress less checking, so it should be
faster?

howa <howachen@gmail.com> wrote in news:1181917118.297796.294670
@i38g2000prf.googlegroups.com:

> e,g,
>
> Select * From TableA LEFT JOIN TableB ON xxx
>
> vs
>
> Select * From TableA JOIN TableB ON xxx
>
>
> Since I believe LEFT JOIN requiress less checking, so it should be
> faster?

I don't think it really matters, as long as everything is indexed properly.
LEFT vs INNER vs RIGHT etc is more about being able to return important
data that might have some columns with NULL or missing values; it's not
really a speed decision at all.

Unless this was just a purely academic question, in which case i've missed
the point

On Jun 15, 4:13 pm, Good Man <h...@letsgo.com> wrote:
> howa <howac...@gmail.com> wrote in news:1181917118.297796.294670
> @i38g2000prf.googlegroups.com:
>
> > e,g,
>
> > Select * From TableA LEFT JOIN TableB ON xxx
>
> > vs
>
> > Select * From TableA JOIN TableB ON xxx
>
> > Since I believe LEFT JOIN requiress less checking, so it should be
> > faster?
>
> I don't think it really matters, as long as everything is indexed properly.
> LEFT vs INNER vs RIGHT etc is more about being able to return important
> data that might have some columns with NULL or missing values; it's not
> really a speed decision at all.
>
> Unless this was just a purely academic question, in which case i've missed
> the point

Other things being equal, JOIN is quicker.

On Jun 15, 11:50 pm, strawberry <zac.ca...@gmail.com> wrote:
>
> Other things being equal, JOIN is quicker.- Hide quoted text -
>

Can't get your meaning?

On Jun 15, 5:10 pm, howa <howac...@gmail.com> wrote:
> On Jun 15, 11:50 pm, strawberry <zac.ca...@gmail.com> wrote:
>
>
>
> > Other things being equal, JOIN is quicker.- Hide quoted text -
>
> Can't get your meaning?

Taking the example of the schema I posted yesterday in another thread,
the following queries will both produce the same result - but the LEFT
JOIN takes twice as long (although I'm not sure of the significance of
that ratio):

SELECT * FROM food_category fc
LEFT JOIN food f ON f.id = fc.food_id;
(0.034 secs)

SELECT * FROM food_category fc
JOIN food f ON f.id = fc.food_id;
(0.017 secs)

LEFT JOINS and RIGHT JOINS are syntactically identical, therefore we
should expect them to take exactly the same time:

SELECT *
FROM food f
RIGHT JOIN food_category fc ON f.id = fc.food_id
(0.034 secs)

However, the question is misleading because it's rare that one type of
join can, with certainty, be used in place of another. In this
example, it's merely accidental that both queries produce the same
result set.

On 6 16 , 12 34 , strawberry <zac.ca...@gmail.com> wrote:

> However, the question is misleading because it's rare that one type of
> join can, with certainty, be used in place of another. In this
> example, it's merely accidental that both queries produce the same
> result set.

usually, inner join can be replace by left join, provided that we
don't allow null value on some fields

now, considering the inner and left join,

since left join will always return the rows in the left table, so i
believe...

less cross checking => generally faster

howa <howachen@gmail.com> wrote:
> On 6 16 , 12 34 , strawberry <zac.ca...@gmail.com> wrote:
>
>> However, the question is misleading because it's rare that one type of
>> join can, with certainty, be used in place of another. In this
>> example, it's merely accidental that both queries produce the same
>> result set.
>
> usually, inner join can be replace by left join, provided that we
> don't allow null value on some fields

Which is - of course - complete nonsense.

First you ask the database to add filler rows (those with NULLs)
and then you ask it to remove them (in WHERE). Do you really think,
the database gets faster if you put some extra work on it?

> now, considering the inner and left join,
>
> since left join will always return the rows in the left table, so i
> believe...
>
> less cross checking => generally faster

Nope. Also LEFT JOIN has to check if a matching row exists in the
other table. And insert NULLs otherwise. So if the JOIN order is
the same, then LEFT JOIN does the same number of lookups in the
"right" table than an INNER JOIN.

But once more: INNER and OUTER JOIN in general produce different
results. So there is absolutely *no* point in asking which one
is faster. The choice is based on the expected result anyway.


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/