mos wrote:
> Another user privately e-mailed me the errors of my ways.<g>
>
> Here is the problem (returns 0 rows):
>
>> > select * from table1 t1 left join table2 t2 on
>> > t1.date1=t2.date2 where
>> > t2.date2 is null
>> > where t2.name='Smith'
>
>
> Here is the solution:
>
> select * from table1 t1 left join table2 t2 on t1.date1 = t2.date2 AND
> t2.name = 'Smith' WHERE t2.date2 is null;
>
> I had to move another reference of t2 from the Where clause to the Join,
> namely t2.name='Smith' gets moved to the join. I thought I had done this
> before but I left one reference in the Where clause that prevented any
> rows from being returned.It appears the conditional in the join clause
> gets executed before the rows are joined, and the Where clause gets
> executed after the join.
>
> Mike
David Turner wrote:
It appears mysql recommends against the solution given
to you
http://dev.mysql.com/doc/refman/5.0/en/join.html
You should generally not have any conditions in the
ON part that are used to restrict which rows you want
in the result set, but rather specify these conditions
in the WHERE clause. There are exceptions to this
rule.
If you could send a sample of table1, table2, and the
result set you want to arrive at I'd appreciate it.
Dave
There has been some debate on this list as to whether or not that's good advice,
but, in any case, this is one of the "exceptions to this rule" mentioned in the
manual. This is because of the way the LEFT JOIN works. Rows in the left table
which match restrictions in the WHERE clause are guaranteed to show up in the
results, whether or not there is a corresponding row in the right table. That
correspondence is determined by the ON clause. If there is no matching row on
the right side of the join, as determined by the ON clause, a single matching
row is created with NULLs in the columns of the table on the right. Conditions
in the WHERE clause referring to the table on the right are applied after the
join. Hence,
SELECT *
FROM table1 t1
LEFT JOIN table2 t2 ON t1.date1=t2.date2
WHERE t2.date2 IS NULL
AND t2.name='Smith';
is unlikely to return any results, because t2.date2 is only NULL if there is no
matching row on the right, but then t2.name is also NULL.
To work, this query must be
SELECT *
FROM table1 t1
LEFT JOIN table2 t2
ON t1.date1=t2.date2 AND t2.name='Smith'
WHERE t2.date2 IS NULL;
That is, rows on the right only match if they have the correct date2 *and* the
correct name. If there are no matching rows, then we get NULLs. The WHERE
clause restricts the results to only the non-matching case. You see?
See the manual for details
<http://dev.mysql.com/doc/refman/5.0/en/left-join-optimization.html>.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
