On Mon, Oct 05, 2009 at 02:02:51PM +0200, Tim Lind scratched on the wall:
> Hi
>
> 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?
It sounds to me like you're mixing syntax.
If use the JOIN syntax, the condition must be part of the JOIN/FROM
clause:
SELECT * FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t2_id WHERE ...
If you use the WHERE condition, there is no "JOIN":
SELECT * FROM t1, t2 WHERE t1.t1_id = t2.t2_id ....
Which also means there is no way to make this a "LEFT" join without
explicitly dealing with the IS NULL case. I'm fairly sure SQLite
doesn't have a special syntax like Oracle and other old-school
pre-JOIN-syntax databases (or, if it does, it is well hidden in the
docs).
If you're trying something like this:
SELECT * FROM t1 LEFT JOIN t2 WHERE t1.i = t2.i
That's not going to do what you think it is going to do. You're
asking for an unconditional LEFT JOIN, then you're putting a
condition on the result of that join. Thanks to 3VL, that condition
will get rid of any rows with a NULL.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
