Re: [PHP] Are left joins more efficient?

[Pete James]

THese are very different things, generally.  The first join is an INNER 
join.  It pulls all records that have the matching ids.  The left join 
is an OUTER join.  It pulls ALL records from table1, and any matching 
records from table2.   This is useful if you want customer and address 
info, but want all customers, regardless of whether they have addresses 
or not.

They are very different.

If you are actually meaning to ask about whether this

SELECT * FROM table1,table2 WHERE table1.id=table2.id;

is better or worse than (note the lack of LEFT)

SELECT * FROM table1 JOIN table2 ON table1.id=table2.id;

then that is dependent on your database.  I can't comment on MySQL, but 
PostgreSQL, for instance, will usually optimize the second query better. 
 But it is highly dependent on what you're doing and where you're doing it.

HTH.
Pete.
[EMAIL PROTECTED] wrote, On 2003/Aug/25 10:59 AM:
Hello,

I program for a website that gets massive loads of traffic. Optimisation has
become an important issue lately.
At the moment, all queries on the website follow the same format when
joining tables:
SELECT * FROM table1,table2 WHERE table1.id=table2.id;
My question is, would this format be more efficient?
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
Over the last couple of years I have read and heard two different answers.
Years ago it was said that doing Left Joins are faster and more efficient.
But with recent updates to MySQL I have read that both queries are broken
down and optimised the same way by MySQL.
Any thoughts? I havn't come across any comparisons on the web, so any
answers would be appreciated.
(couldn't find any mysql specific groups so i'm posting in the next best
thing!)
Thanks

-Dennis







--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php