[TransWarp] Topics for tomorrow, and beyond

[Phillip J. Eby]

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.

Indeed.