Hi all,

we've run into some counterintuitive (to us) behaviors of SQL. What we wanted is to obtain a resultset which contained all id's from table1 that had the property yy.lproperty, leaving out the records for which lproperty was .F. .What we got is all ids from table1 with .NULL. for yy.id and yy.lproperty from table2 for the records that had lproperty = .F. .This is easily fixed by using an INNER JOIN instead of a LEFT JOIN. We felt however that the result should be the same, because of the where clause. We always thought that the where clause affected all records in the resultset, so the conditions would be applied after the tables had been joined. It seems to work exactly that way with INNER JOINs, but not with LEFT JOINs. In LEFT JOINs the where clause seem to act as though they had been included in the JOIN-condition, ie the condition applies to the table to be joined first. That way, it decides first which records from table2 conform to the where clause condition. So, rather than deleting the record from the resultset, the join just fails and .null.'s result.

SELECT xx.id,yy.id,yy.lproperty;
FROM table1 xx ;
LEFT JOIN table2 yy ON xx.id == yy.id ;
WHERE yy.lproperty

and

SELECT xx.id,yy.id,yy.lproperty;
FROM table1 xx ;
LEFT JOIN table2 yy ON xx.id == yy.id and yy.lproperty

vs.

SELECT xx.id,yy.id,yy.lproperty;
FROM table1 xx ;
INNER JOIN table2 yy ON xx.id == yy.id ;
WHERE yy.lproperty

Can someone enlighten us on this one?

regards,
derk schutte