Join types

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gints Plivna - 25 Jan 2008 11:04 GMT

I'm a bit studying join types and trying to make a visualisation of
relations among them. As a result I've created an ER diagram
describing relations among them and it can be found here
http://gplivna.blogspot.com/2008/01/sql-join-types-im-studying-bit-sql.html
I've tried to find something like that using google however the best I
could get was textual description. I'm not mathematician and studied
set theory a bit 10 years ago in university and almost all have no
forgotten :) so maybe it has some problems from set theory viewpoint.
So question is - is it generally ok? If you know anything similar
please add link either here or in my blog post. All comments welcome!
Thanks!
Gints

Reply to this Message

Tegiri Nenashi - 25 Jan 2008 17:03 GMT

> I'm a bit studying join types and trying to make a visualisation of
> relations among them. As a result I've created an ER diagram
[quoted text clipped - 7 lines]
> Thanks!
> Gints

Join is indded the most important operation in the RM. However. SQL
classification of joins is quite ad-hock and not particularly
illuminating. There are many more interesting joins around.

The "normal" natural join is something that can be described as set
intersection join. There are also set containment join (aka relational
division) and symmetrized form of set containment join -- set equality
join.

The other direction is comparing joins in Binary Relation algebras
with joins in RA. In Relation algebras join is not commutative and is
not a generalisation of set intersection.

Reply to this Message

JOG - 25 Jan 2008 17:19 GMT

> I'm a bit studying join types and trying to make a visualisation of
> relations among them. As a result I've created an ER diagram
[quoted text clipped - 7 lines]
> Thanks!
> Gints

Having only looked at your breakdown briefly, I can't give you much
comment (although I thought your time-bomb discussion of natural joins
was entertaining). However, I would say that in general I view natural
joins, equi-joins, etc. as specializations of the generalized theta
join. I'd be interested if other's share this perspective.

Reply to this Message

Bob Badour - 25 Jan 2008 17:46 GMT

>>I'm a bit studying join types and trying to make a visualisation of
>>relations among them. As a result I've created an ER diagram
[quoted text clipped - 13 lines]
> joins, equi-joins, etc. as specializations of the generalized theta
> join. I'd be interested if other's share this perspective.

I do not share the perspective. I prefer to view theta join as natural
join followed by restrict.

One might argue it's just a question of one's choice of primitives. I
would argue that both natual join and restrict are simpler operations
than theta join.

Reply to this Message

Jonathan Leffler - 26 Jan 2008 05:06 GMT

>>> I'm a bit studying join types and trying to make a visualisation of
>>> relations among them. As a result I've created an ER diagram
[quoted text clipped - 18 lines]
> I do not share the perspective. I prefer to view theta join as natural
> join followed by restrict.

Can you explain how that works? I can see theta join as cartesian
product followed by restrict, but I don't see how you do a 'greater
than' join with a natural join -- unless you rename one (or both) of the
join terms so that natural join degenerates into cartesian product and
then you restrict on the renamed terms. But that's not an obvious use
of a natural join, I think.

> One might argue it's just a question of one's choice of primitives. I
> would argue that both natural join and restrict are simpler operations
> than theta join.

Signature

Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/

publictimestamp.org/ptb/PTB-2365 sha512 2008-01-26 03:00:05
AB90FEBA17BC6DD2B41824F751D70E037E6DC63C810FF209B17E01B7883E9DFB1D6E34
51F56B1991A26C63BECE9CBC0C696517C3EE4F0B0C9056723D17F3EDE

Reply to this Message

Bob Badour - 26 Jan 2008 14:22 GMT

>>>> I'm a bit studying join types and trying to make a visualisation of
>>>> relations among them. As a result I've created an ER diagram
[quoted text clipped - 25 lines]
> then you restrict on the renamed terms. But that's not an obvious use
> of a natural join, I think.

Cartesian product is only a special case of natural join. How do you
express the inequality if the join attributes have the same name? What
is the corresponding value in each tuple?

>> One might argue it's just a question of one's choice of primitives. I
>> would argue that both natural join and restrict are simpler operations
>> than theta join.

Reply to this Message