> 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. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users