> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
