INNER JOIN vs LEFT JOIN WHERE x IS NOT NULL

[Jabop]

I have read some debate on a few sites about the two joins. Most state that LEFT JOIN WHERE x IS NOT NULL is faster and more efficient than an INNER JOIN.

 

I've done a few benches with a few of the tables that I have, at only about 14k records - which I understand isn't much - but I got the same results with both queries.

 

SELECT ac.Referrer, a.Name

FROM advertisementclicks AS ac

LEFT JOIN advertisements AS a ON a.ID = ac.AdvertisementID

WHERE a.ID IS NOT NULL

 

Showing rows 0 - 29 (13,975 total, Query took 0.0011 sec)

 

SELECT ac.Referrer, a.Name

FROM advertisementclicks AS ac

INNER JOIN advertisements AS a ON a.ID = ac.AdvertisementID

Showing rows 0 - 29 (13,975 total, Query took 0.0011 sec)

 

Does anyone have any information on this, care to debunk or back this up, or elaborate?

[fenway]

There's no difference in this case, the optimizer will figure that out right away... so you're not really testing anything.  The difference will come about when there are NULL values in the join-ed field during the LEFT JOIN.