General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Sql and where clauses
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
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only