[TransWarp] Topics for tomorrow, and beyond
Phillip J. Eby
pje at telecommunity.com
Thu Oct 16 10:35:08 EDT 2003
At 10:53 AM 10/16/03 +0300, alexander smishlajev wrote:
>Phillip J. Eby wrote, at 16.10.2003 7:30:
>>Note that the exists join can be emulated via an outer join, combined
>>with an assertion that the columns on the target are not null.
>sorry, i do not understand why do you need an outer join here. this is
>exactly what a normal, inner join does.
As it turns out, I was wrong anyway. Not only can exists not be emulated
with outer join, it would be a bad idea to do so, because outer joins are
not associative and it limits the ability to re-order joins. So, exists
should instead be represented as a wrapper over the "existence" part that
effectively either does a "group by" on the join columns, or a "select
distinct" keeping only the join columns.
>'exists' differ from joins (both inner and outer) in that it does not make
>a production of the "select" table and "exists" table. if you are going
>to emulate 'exists' with a join, you will probably have to aggregate rows
>in the join result, won't you?
Yes, that's correct.
>>Plus, don't forget UNION... perhaps INTERSECT and DIFFERENCE as well.
>>We don't use these right now because Sybase didn't do them years ago, but
>>there were times that I really wanted them...
>just curious: what intersect is needed for?
I don't remember right now; five years ago it seemed like a brilliant
solution to a query problem I had, but Sybase didn't support it, so I came
up with a tricky alternative using count, group by, having, and some other
junk. Since then I haven't thought about intersect much, except to notice
that Gadfly supports it, even though a big company like Sybase
didn't. :) I believe the latest Sybase has it, though.
>since outer joins appeared, i don't remember if i ever had a real need in
>union, and i've never used intersect or difference, although i think i can
>imagine how difference may be useful if we don't have outer joins.
More information about the PEAK