On Monday, 28 October, 2019 11:19, James K. Lowden <jklow...@schemamania.org> wrote:
>When wrestling with this topic, it's useful to remember that ON doesn't >constrain the outer table: > > FROM R LEFT OUTER JOIN S > ON R.key = S.key AND R.col = 'foo' >Here, > AND R.col = 'foo' >while valid syntax (unfortunately), has no effect. R is the outer >table, and all rows of the outer table are always produced, no matter >what ON says about it. Saying that it has no effect is incorrect. It does constrain the inner loop candidates. It has no effect on the outer loop candidate selection because we are already inside the inner loop before that condition is evaluated. In other words, there can be no matching t2 tuple if the t1 tuple does not have t1.a == 1. 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 This sort of thing is useful, for example, if t2 represents data about the neck length of giraffes, and t1.a tells the animal type (1 == giraffe). Clearly things which are not giraffe's do not have a giraffe neck length so this constraint is properly part of the inner candidate selection. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
