Re: LEFT joins

Jochem Maas wrote:

I'm not but his original query used JIONs and a LEFT JOIN is (IMHO) the easiest to understand. I didn't have the presence of mind to rewrite the query using a simple WHERE clause - hope your tip helps him. btw: can anyone say if the LEFT JOIN or the alternative WHERE statement (in general?) is faster?

I can't speak to the speed, although since JOINs (can) do more than simple matching, I'd say the where condition may turn out a little better (after crossing fingers that mysql will use the proper indices in all cases). An example of something I can't think of how to write into a where clause (easily, at least): from http://dev.mysql.com/doc/mysql/en/JOIN.html : " If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table: mysql> SELECT table1.* FROM table1

    ->        LEFT JOIN table2 ON table1.id=table2.id
    ->        WHERE table2.id IS NULL;

" The 'explain' syntax can give you some idea of this, as well as help you in optimizing indices and queries. http://dev.mysql.com/doc/mysql/en/EXPLAIN.html Cheers, -- - Martin Norland, Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital.