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;
"
I remember reading that now - last time I did major surgery with MySQL - it had to do with loads of IFNULL and CONCAT statements - thats where the specifics of the LEFT JOINS (null values produced) came in (must have stuck in my head).
The 'explain' syntax can give you some idea of this, as well as help you in optimizing indices and queries.
never did quite understand the explain output. :-)
http://dev.mysql.com/doc/mysql/en/EXPLAIN.html
Cheers,
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
