I think they are equivalent, if you look closer. SELECT FROM a LEFT JOIN b ON a.x=b.x WHERE b.y=5 -is- equivalent to SELECT FROM a JOIN b ON a.x=b.x AND b.y=5 SELECT FROM a JOIN b WHERE a.x=b.x AND b.y=5 SELECT FROM a LEFT JOIN b ON a.x=b.x AND b.y=5 WHERE b.y IS NOT NULL
All the above are semantically equivalent. When there is only one LEFT JOIN, the presence of any non-null non-alternative condition on the joined table in the WHERE clause transforms it in an INNER join. There is no other way to have a non-null value except if the row exists. The reciprocal is not true of course. I don't know how difficult it is to compute the -OR- closure, as it is more difficult. But for an imperative non-null condition, I did expect the WHERE condition to be ported to the ON lookup for optimisation. -- Sent from: http://sqlite.1065341.n5.nabble.com/ _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
