op 05-01-2018 17:23 schreef David Raymond op david.raym...@tomtom.com: >> Anyway the two queries return the same set of rows. > >> This test also show a small semantic difference in the two queries. >> The set of rows is the same but the second query leaves certain >> details null if only one of the OR conditions is true. That occurs in >> row 1. > > You're contradicting yourself there. If there's a difference in the results > then they're not the same set of rows. > > We'll just look at the "all 1" case. a has (1, 1, 1), b has (1, 1) and c has > (1, 1) > > Best if viewed in a fixed-width font > > Step by step version 1: > > a left join b on b = ab > > a b > a ab ac b d > (1, 1, 1) (1, 1) > > result: > a ab ac b d > (1, 1, 1, 1, 1) > > left join c on c = ab > > c > a ab ac b d c d > (1, 1, 1, 1, 1) (1, 1) > > result: > a ab ac b d c d > (1, 1, 1, 1, 1, 1, 1) > > where b.d in (1, 2, 3) or c.d in (4, 5) > > b.d is 1, so it passes > result: > > a ab ac b d c d > (1, 1, 1, 1, 1, 1, 1) > > The OR worked. We got the values from both tables b, and c and because one of > them was correct. > > > > Now, Step by step version 2: > > a left join b on b = ab and b.d in (1, 2, 3) > > a b > a ab ac b d > (1, 1, 1) (1, 1) > > result: > a ab ac b d > (1, 1, 1, 1, 1) same so far (but only by coincidence) > > left join c on c = ac and c.d in (4, 5) > > c > a ab ac b d c d > (1, 1, 1, 1, 1) (1, 1) > > the ON condition doesn't match. Since this is an outer join, and there were no > matches for the row in the left side, nulls are included > > result: > a ab ac b d c d > (1, 1, 1, 1, 1, null, null) > > where b.d is not null or c.d is not null > > b.d isn't null, so that passes. > result: > > a ab ac b d c d > (1, 1, 1, 1, 1, null, null) > > So we get a row saying that a matched something in b, but we're throwing out > the value from the c table, which is not what we wanted.
Thanks, all clear except this last line. Did we not want the value to be thrown out. Or not want the value? It depends on that which query is favourite. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
