Thursday, December 21, 2006

Re: Select you crazy Query

Are you saying the first query is wrong cause it did not get the results you expected? Cause I don't see anything technically wrong with it. A left outer join does not mean that at the end of the query (whatever it may be), you will get all foos. What you get in the end depends on your WHERE clause.

Break the first query down and you will see how it was arrived at. The cross product of the tables based on the ON clause of the LEFT OUTER JOIN "did" contain foo(1) but the WHERE clause which was applied over the cross product eliminated that record. Hence the result.

The behavior is standard and not MySQL specific. Both those queries on a different engine should return the same behavior. Also, I don't see why the ON clause should remember the composite primary key.

More on left outer join here.

No comments: