On Mon, 28 Oct 2019 12:10:38 -0600
"Keith Medcalf" <[email protected]> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
