Tim Lind wrote: > I have a query that is using a left join, with a where clause, and the > results I expect are not returned because the one table doesn't have a > related record. > If I put the constraint in the on clause of the query instead, the > expected results are returned with the null record of the related > table. > > Is this standard behaviour of SQL or specific to SQLite?
I believe this is standard behavior. Imagine you have select * from table1 left join table2 on (condition1) where condition2; Condition1 is used to build the source recordset. For every pair of rows from table1 and table2 for which condition1 holds, a corresponding "concatenated" row is added to that recordset. If for some row in table1 there is no row in table2 that satisfies the condition, a row is added to the recordset with all table2 fields set to NULL. Condition2 is used to filter the source recordset. Only rows matching this condition are returned by the statement. Note that some of the source rows have lots of NULLs, and that most comparisons involving NULLs are false. That's why, unless you are careful, the condition in WHERE clause that mentions table2 fields is likely to filter out all table1-only records, and render left join pointless. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
