If I remember correctly I found out by mistake that the ON doesn’t even have to
relate to the JOIN it follows as long as it fits in with the WHERE. E.g.
SELECT * FROM Tbl1 INNER JOIN Tbl2 ON Tbl3.Col=Tbl1.Col INNER JOIN Tbl3 ON
Tbl2.Col=Tbl1.Col;
Something like that.
________________________________
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of
James K. Lowden <jklow...@schemamania.org>
Sent: Monday, October 28, 2019 10:32:21 PM
To: sqlite-users@mailinglists.sqlite.org <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] LEFT JOIN: ON vs. WHERE
On Mon, 28 Oct 2019 12:10:38 -0600
"Keith Medcalf" <kmedc...@dessus.com> wrote:
> CREATE TABLE t1 (a, b);
> INSERT INTO t1 VALUES(1,2);
> INSERT INTO t1 VALUES(2,3);
> INSERT INTO t1 VALUES(1,3);
> CREATE TABLE t2 (b, c);
> INSERT INTO t2 VALUES(2,3);
> INSERT INTO t2 VALUES(3,4);
> select * from t1 left join t2 on t1.b == t2.b and t1.a == 1;
> 1|2|2|3
> 2|3||
> 1|3|3|4
Thank you, Keith. What you're saying is that when ON applies to the
outer table, it still constrains the JOIN. The tuple
2 3 3 4
would have been produced, but for the ON restriction
t.a = 1
> This sort of thing is useful
I don't doubt it. I can't remember ever having written a query like
that (not knowing that's what it would do). I would probably have
expressed the giraffe-neck problem as
select * from t1
left join ( select * from t2 where b <> 1 ) as t2
on t1.b = t2.b
because usually, in domains I worked with, the constraint on the inner
table wasn't a function of the join criteria, but of some type or group
or catagory column, or date.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
